I have known Tony Kirn for over 8 years. Not only we do share a common interest in Oracle Databases but we are also great buddies.

Yes my opinion may be considered bias, but he is highly regarded throughout the DBA community within Australia.

Tony has been working in IT since 1996 and has worked from the ground up from running cables and tinkering with hardware until finding his real passion for Oracle databases.

With his expansive experience across computing telephony, banking and finance, government industry he has shown appreciation to understand all the layers of which databases are rests upon. This has allowed him to make some of the most critical and innovative decisions throughout his career as a database professional.

He has been involved in the first successful deployment of Enterprise Manager 10g in Australia at Telstra and recently was the tech lead responsible for delivering Enterprise Manager 12c. He will be presenting at Oracle Open World this year.

So if you are attending I strongly recommend you to attend as he will provide some invaluable advice on his experience on deploying Enterprise Manager in complex networks.

Yes I admit, I have been one of those DBAs who has hacked away at the tnsnames.ora and has faced this issue in past. Although not widely known or used, there are solutions to this issue.

Solution A:
You can use the netca wizard, if you prefer to click away, however you need to ensure that you have X session enabled.

Solution B: (I prefer this option!)
A quicker and easy way of creating multiple entries for TNS is to use the netcaresponse file with silent mode.

You can use the netca response file to create listener(s) as well but in this example, we will concentrate on looking at creating TNS aliases using a response file. This is not a new feature and has been available in previous versions of Oracle.

1. I want to create the following TNS alias entries for the following:

2. Take note of the following parameters

NSN_NUMBER – This is the number TNS alias(es) you want to createNSN_NAMES – List all the TNS alias(es) namesNSN_SERVICE – List all the TNS service(s) associated with the TNS alias(es)NSN_PROTOCOLS – List the protocol,host and port for each TNS alias

3. The following response file I will create is called 11203_netca.rsp and I have configured it as below:##################### 11203_netca.rsp ######################
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]
INSTALL_TYPE=""custom""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
#--------------- Modify settings in this section ---------------NSN_NUMBER=2 NSN_NAMES={"GREY","GREY_STBY"} NSN_SERVICE={"GREY","GREY_STBY"} NSN_PROTOCOLS={"TCP;lnx161;1521","TCP;lnx162;1521"}
#---------------------------------------------------------------
#######################################################

When you want to do a verification of current activity in the database, the following sos_sessions_active.sql script will list information on active sessions, blockers, associated events, impacted objects, users, program and services that are currently running across all instance(s) in the database (i.e. this script can run against a single instance or RAC database).

An example of this report can be found here:(Click on image to zoom in)

Historical Sessions

At times, identifying performance issues in real time can be challenging. By the time the DBA is called upon to investigate the issue disappears. Rather then waiting for the issue to resurface, with Active Session History introduced in 10g, the feature has become invaluable source to allow DBAs to perform post analysis and of sessions captured in the past.

Using the sos_sessions_history.sql script, by specifying the time period you wish to review, the report will return details on sessions and associated events.

NOTE: The columns “machine” and “sql_opname” is not available to be reported againstDBA_HIST_ACTIVE_SESS_HISTORY in version 10g and 11.1. So the sos_sessions_history.sql script will be required to edited to remove these columns from the query is executing against versions earlier then 11.2.

A snippet from a sample output can be found here:(Click on image to zoom in)

In the pre-11g days of ASM, I was hesitant to use ASMCMD (command line utility to perform ASM tasks)… perhaps in its earlier days, it was a combination of limited functionality and my comfort in connecting to the ASM instance via sqlplus to perform administrative tasks. But now in 11g, I have grown the desire to use ASMCMD and have found it to be more useful then I have first envisaged.

In 11g, some of the new functionality now offered through ASMCMD includes the ability to perform:

With the more frequent use of ASMCMD, I got tired of switching between the ASM environment and DB environment settings, as a result, I have created an ASM wrapper script which calls asmcmd (ie. works when ASM is mounted or running) with the ability to preserve your DB environment setting.

Example of using this script are provided below.

Change to the directory to where the script sos_asm.sh was downloaded to and set executable permission accordingly:

ADR (Automatic Diagnostic Repository) was introduced in 11g and is a datastore for diagnostic information for oracle components such as database, asm, listener and scan listeners. It stores trace files, logs in both clear text and xml format.

ADR does allow the capability to see alert (and listener) logs in realtime.

For example if I wanted to view in realtime the alert log for Database called RED, I would do the following:

When I run the “crsctl stat res -t” and it print lines and lines that go on forever of all the resources for your cluster, I find that at times you can easily oversee resources that are in a pending or bad status that need special attention. Don’t get me wrong, it is nice to know about all your resources. However, there is a better way that you can do a status check, and report only the pending or bad resources.

Note that the “crsctl stat res -t -w “((TARGET = OFFLINE) or (STATE = OFFLINE)” will only report resources with the OFFLINE target or state. It will not report other target or state information. Hence I will show you below in an example on how to capture resources with pending (starting, intermediate) or bad (offline, unknown etc…) status. Therefore I want to report all resources with the target or state that is not ONLINE. In addition, I want to also exclude that annoying gsd (ora.gsd) resource from the check, as we are not hosting any oracle 9i RAC database on this cluster, therefore the target and state for this resource will always show as being offline.

OPatch utility now has just got a little fancier and more user friendly. Download latest OPatch (Patch 6880880) for the Oracle software release (eg. 10.2, 11.1, 11.2) from My Oracle Support and extract to the appropriate Oracle software directory.
NOTE: The OPatch utility can be applied to Database, Grid Control (OMS and Agent), Grid Infrastructure (ASM and CRS) software home directories.

In the below example the database software release is 11.2 and runs on Linux 64 bit platform, so to update the OPatch utility, the patch file 6880880_112000_Linux-x86-64.zip was downloaded.

Below are just a few examples of what you can now do with the more recent versions of OPatch.

A. To download and install latest OPatch version

1. Verify the version of OPatch currently installed, simply run:
SYNTAX:export ORACLE_SID=<ORACLE_SID>; . oraenv
$ORACLE_HOME/OPatch/opatch version

B. List Detailed Information About a Patch Before Applying It

The OPatch utility can be used to verify details of a patch before proceeding to apply to the Oracle Home directory.
Such information that can verified but not limited to are:
• Check if patch is a Rolling Patch
• Check if patch is a Patchset Update (PSU)
• Check if patch can be run with “opatch auto” option
• Check OS platform the patch can be applied on
• Actual actions and/or steps that patch contains without applying the patch. This include detailed information of files it touches, copies and relinks.

There is an ongoing focus for businesses to minimise downtime and increase operational continuity. With such challenges, IT staff are constantly under pressure to meet such demands. For DBAs, backup and recovery is one of the key areas which can be reviewed to ensure that a faster approach to recovery can be achieved to reduce downtime.

An RMAN feature called Incremental Merge (also referred to as Incremental Updated) Backups can significantly reduce recovery time if configured correctly. Perhaps not as widely used but has been around since 10g, it is the ideal backup methodology for a VLDB (Very Large Database). How it works is that the image copies of the data files are created and incrementals are then applied rolling forward the image copies after each backup operation. It is also important to enable fast incremental backups with BCT (Block Change Tracking). For more detailed information on BCT, please refer to the Pythian Whitepaper written by Alexander Gorbachev.

The table below lists the configuration that will be used for the following demonstration:

As the database is continuing to run from where the recovered image copies are located, there may be a desire to restore it back to its original location. This is optional, however it is recommended if you are running image copies from FRA (Fast Recovery Area).

NOTE: If this is the first time of running the backup to new location, it will create the image copies of the datafiles.

2. Repeat the previous step for any future or subsequent backups until a time can be scheduled to switch back.

3. When a planned time has been scheduled and switch back can be performed, shutdown database and startup in MOUNT mode.[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> shutdown immediate
SQL> startup mount

4. Switch database to use image copy and then perform recovery. The database will now be started using image copies which is now configure in the original data file location.RMAN> set echo on;
switch database to copy;
run {
recover database;
alter database open;
}

Grid Control 11g interface may have the same look and feel as the Grid Control 10g, but under the hood it is quite different. In version 10g, the OMS was a J2EE (OC4J) application deployed on the Oracle Application Server. Whereas in version 11g, the OMS is deployed on Web Logic Server (WLS) .

For this post, I have provided some simple steps on how to install the Grid Control with minimal pain.

The table below lists the configuration that will be used for the following demonstration.

Product Software

Version

Host

Environment Settings

GC Repository Home

11.2.0.1

mama.earth.com

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

GC Repository SID

11.2.0.1

mama.earth.com

ORACLE_SID=GCREP

JDK

1.6 Update 18

oem.earth.com

JDK_HOME=/u01/jdk16

Middleware Home

11.1.0.1

oem.earth.com

MW_HOME=/u01/app/oracle/Middleware

Webtier

11.1.1.2

oem.earth.com

WT_HOME=/u01/app/oracle/Middleware/Oracle_WT

Web Logic Server (WLS) Home

10.3.2.0

oem.earth.com

WL_HOME=/u01/app/oracle/Middleware/wlserver_10.3

WLS Instance Base

10.3.2.0

oem.earth.com

WLS_INSTANCE_BASE=/u01/app/oracle/gc_inst

WLS Instance Home

10.3.2.0

oem.earth.com

ORACLE_INSTANCE=/u01/app/oracle/gc_inst/WebTierIH1

WLS Domain (GCDomain)

10.3.2.0

oem.earth.com

WLS_DOMAIN=/u01/app/oracle/gc_inst/user_projects/domains/GCDomain

OMS

11.1.0.1

oem.earth.com

ORACLE_HOME=/u01/app/oracle/Middleware/oms11g

Agent

11.1.0.1

oem.earth.com

ORACLE_HOME=/u01/app/oracle/Middleware/agent11g

* SYSMAN is actually the application schema created in the GC. This is also the super administrator for logging into the EM Console.

SECTION 1 –PRE- INSTALLION TASKS

1. Verify the OS platform and supported Database version for the Grid Control Repository that is intended to be installed is supported. See My Oracle Support Note 412431.1 for further details.

2. Install the Database software and create a Database for the Grid Control Repository. A good example can be found here:

SECTION 3 – WEBLOGIC INSTALLATION

NOTE: For this example, the installation will be performed on linux 64-bit platform, hence the Generic file version will be downloaded. If running windows (32bit – x86) or Sun (64bit – SPARC) then please download the alternative as listed below.

SECTION 5 – Install Grid Control Software

(NOTE: Under the Full Installers (Agent,Repository, OMS and Management Packs) section, download for the appropriate files for your platform. In this demonstration the Linux 64 bit version will be the example used.)

2. Extract all files in the same directory where the files were downloaded to:

4. In the same directory where files were extracted to, execute the runInstaller to launch the Grid Control Installation Wizard:Option 1
Run with no variable set:

oracle@oem[]$ cd install
oracle@oem[]$ ./runInstaller

Option 2
If the server has multiple hostname or alias of where the Grid Control installation is to be installed to, the ORACLE_HOSTNAME can be used to forced the Grid Control to use the preferred name

oracle@oem[]$ ./runInstaller ORACLE_HOSTNAME=<PREFERRED_NAME>

For example:

oracle@oem[]$ ./runInstaller ORACLE_HOSTNAME=oem.earth.com

5. The Grid Control Installer screen now appears.

6. Register for Security Updates or leave default and click on Next.

7. To continue click on Yes, if you wish to ignore setup for notification of security updates

8. Leave default to Skip Software Updates and click on Next.

9. The default option of Install a new Enterprise Manager system is selected. Click on Next to continue.

10. Prerequisite checks will be performed.

11. Any failures or warning will be reported. Resolve issues according and click on Next.

12. Enter Passwords for the Weblogic Domain Administrator (weblogic) and Node Manager (nodemanager) and then click on Next.

13. Enter the database connection details for where the Grid Control Repository will be deployed to.

If the following error message below is reported, the installation has detected existing metadata objects in the Database for DB control. Otherwise continue on to the next step.NOTE: The DB control (standalone Enterprise Manager) may have been installed when the database was created)

To deinstall DB control repository, run the following in a new terminal session on the database host of where the Grid Control Repository will reside:

As you may know, SCAN (Single Client Access Name) is a new feature that has been introduced in 11.2 Oracle RAC. To put it simply, the SCAN is actually a single name alias which can be configured for clients to connect to the cluster database. In addition it will also handle the load balancing and failover for client connections. When the nodes in the cluster changes (eg. added or removed), the benefits of SCAN can be realised, as there is no requirement for the client to be reconfigured.

Configuring your clients to used SCAN in 11.2 or later is optional, however when you install the Grid Infrastructure software for RAC, the SCAN is still required to be configured for setup of the cluster.

It is recommended that the SCAN is resolvable to 3 IP addresses. However a minimum of 1 IP Address may be configured. It should also be registered in DNS for round robin resolution.

Reconfiguration of SCAN

There may be situations where reconfiguration of the SCAN for the cluster is required.
Some examples are:

Allocation of IP addresses has changed for SCAN

Adding additional IP addresses for SCAN

DNS Changes associated with SCAN addressing

SCAN name change^

NOTE: ^ SCAN name change may be required if there is conflicting name for another cluster on same network or in DNS, however this would mean that client reference to old SCAN is required be updated. Hence caution should be taken to ensure that SCAN name is unique in DNS and network.

In the below scenario, the following steps will show how SCAN addressing can be reconfigured for a cluster.

At the time of the installation, the DNS registration had not been setup. As a workaround, one of the IP addresses allocated for the SCAN was referenced in the /etc/hosts file. Once the installation was completed and DNS registration updated, the SCAN was then reconfigured to be resolvable through DNS for all 3 IP addresses.

Below is the summary of the current and new configuration for SCAN.

Current

New

SCAN Name

myscan

myscan

SCAN IP Address

172.43.22.89

172.43.22.89
172.43.22.90
172.43.22.91

SCAN entry in /etc/hosts file

Yes*

No

Registered in DNS and configured for round robin

No

Yes

NOTE: SCAN entry in the /etc/hosts file is not recommended, hence the reconfiguration to the NEW is required.