jump to navigation

Cluster Resource Control Filters May 3, 2011

Posted by tamnau in 11g, RAC.
Tags: , , , , , , , , , ,
1 comment so far

In 11.2 RAC, to avoid listing all cluster resources, you can use the filter switch option “-w”.  This can be useful if you only want to look at a specific status of a given resource.

A. List for All Databases Managed by Cluster

In the example, shows the listing of status for all databases managed by the cluster.

[oracle@lnx01] export ORACLE_SID=GRID; . oraenv
[oracle@lnx01] crsctl stat res -t -w "TYPE = ora.database.type"

The output for the above example is as follows:
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.salt.db
      1        ONLINE  ONLINE       lnx01                 Open                
      2        ONLINE  ONLINE       lnx02                 Open                
ora.pepper.db
      1        ONLINE  ONLINE       lnx01                 Open                
      2        ONLINE  ONLINE       lnx02                 Open                

A list below are some type of resources that the filter can set for:

NOTE: The filter from the example can be change from “ora.database.type” to “ora.service.type”. For more options see listing below.

[oracle@lnx01] crsctl stat res |grep "TYPE=" |uniq

Sample output:
TYPE=ora.diskgroup.type
TYPE=ora.listener.type
TYPE=ora.scan_listener.type
TYPE=ora.diskgroup.type
TYPE=application
TYPE=ora.asm.type
TYPE=ora.cluster_vip_net1.type
TYPE=ora.cvu.type
TYPE=ora.database.type
TYPE=ora.service.type
TYPE=ora.database.type
TYPE=ora.service.type
TYPE=ora.gsd.type
TYPE=ora.network.type
TYPE=ora.oc4j.type
TYPE=ora.ons.type
TYPE=ora.scan_vip.type
TYPE=ora.database.type

B. Check Cluster Resources that are offline

Example:
[oracle@lnx01] crsctl stat res -t -w "STATE = OFFLINE"

NOTE: If not using GSD, then it is ok for the following resource to be offline.

Sample output
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.gsd
OFFLINE OFFLINE      lnx01
OFFLINE OFFLINE      lxn02

C. Verifying local resources that are online

Example:
[oracle@lnx01] crsctl stat res -init -t -w "STATE = ONLINE"

NOTE: You can use “-init” to check Local Resources

Sample Output
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
1        ONLINE  ONLINE       lnx01                 Started
ora.cluster_interconnect.haip
1        ONLINE  ONLINE       lnx01
ora.crf
1        ONLINE  ONLINE       lnx01
ora.crsd
1        ONLINE  ONLINE       lnx01
ora.cssd
1        ONLINE  ONLINE       lnx01
ora.cssdmonitor
1        ONLINE  ONLINE       lnx01
ora.ctssd
1        ONLINE  ONLINE       lnx01                 OBSERVER
ora.diskmon
1        ONLINE  ONLINE       lnx01
ora.drivers.acfs
1        ONLINE  ONLINE       lnx01
ora.evmd
1        ONLINE  ONLINE       lnx01
ora.gipcd
1        ONLINE  ONLINE       lnx01
ora.gpnpd
1        ONLINE  ONLINE       lnx01
ora.mdnsd
1        ONLINE  ONLINE       lnx01

D. To check for resources that are Not Online

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.

Example: [oracle@lnx01] crsctl stat res -t -w "((TARGET != ONLINE) or (STATE != ONLINE) and TYPE != ora.gsd))"

NOTE: No output is shown if cluster is up and running.  Otherwise impacted resources will be displayed.

Advertisements

OPatch Friendly May 3, 2011

Posted by tamnau in General.
Tags: , , , , , , , , , , , ,
1 comment so far

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

EXAMPLE:
[oracle@lnx01] export ORACLE_SID=112DB; . oraenv
$ORACLE_HOME/OPatch/opatch version

2. Backup old OPatch utility:
[oracle@lnx01] mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.orig

3. Extract and install latest OPatch to Oracle home directory:
SYNTAX:
unzip p6880880_<RELEASE>_<OS_PLATFORM>.zip –d $ORACLE_HOME

EXAMPLE:
[oracle@lnx01] unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME

4. Re-run step 1 from this section to verify new version of OPatch

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.

SYNTAX:
$ORACLE_HOME/OPatch/opatch query -all |more
EXAMPLE 1:
[oracle@lnx01] cd /home/oracle/download/patches/12311357
[oracle@lnx01] $ORACLE_HOME/OPatch/opatch query -all |more

C. Looking for a Particular Patch Already Applied based on a Bug or Patch Description

To search for a patch based on search string, OPatch can list a detailed description of each patch applied to the Oracle home directory.

SYNTAX:
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep –i "<string>"

This is particularly handy if you want to look for a Patchset Update (PSU) or Daylights Savings Time (DST) patch.

EXAMPLE 1:
[oracle@lnx01] $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep –i “DATABASE PSU”
EXAMPLE 2:
[oracle@lnx01] $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep –i “DST”

D. Cleanup Patch Storage to Reclaim Space

OPatch can now determine and cleanup files in the patch storage that are no longer required. To do so run the following:
SYNTAX / EXAMPLE:
[oracle@lnx01] $ORACLE_HOME/OPatch/opatch util cleanup

Oracle Backup and Recovery for a VLDB (Very Large Database) February 10, 2011

Posted by tamnau in Database, General, RMAN.
Tags: , , , , , , , , , , , , ,
4 comments

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:

Description Value
Database Instance ORACLE_SID=VLDB
Host lnx01.earth.com
RMAN Catalog DB (TNS Name) RMANCAT
RMAN Catalog User rman


SECTION 1 – CONFIGURE BLOCK CHANGE TRACKING

1. To create block change tracking file, run the command in sqlplus as sysdba:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '<path_to_block_change_file_name>';

For example:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u02/oradata/VLDB/bct_VLDB.ctf';

IMPORTANT NOTE: If running in a RAC configuration, the following file needs to be placed on the cluster shared file system.

2. Login to database and verify the location of data files.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> col file_name form a75
SQL> select file_id, file_name from dba_data_files
/
FILE_ID FILE_NAME
---------- --------------------------------------------------
1 /u02/oradata/VLDB/o1_mf_system_7o8kj2gt_.dbf
2 /u02/oradata/VLDB/o1_mf_sysux_2o3mt9jl_.dbf
3 /u02/oradata/VLDB/o1_mf_undotbs_1c5gb8nh_.dbf
4 /u02/oradata/VLDB/o1_mf_users_5v5db3vv_.dbf


SECTION 2 – CONFIGURE RMAN PARAMETERS

1. Register database with RMAN Catalog if using Catalog mode. See example here.

2. Connect to RMAN in catalog (or no catalog) mode.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] rman target / catalog rman/@RMANCAT

3. Configure the preferred RMAN Recovery Window. In the example, the recovery window will be 3 days.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

4. It is also recommended that you enable autobackup of controlfile in RMAN.
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

SECTION 3 – Performing the Incremental Merge (Updated) Backup

1. Perform the backup of the database in RMAN, and this will create an image copy on the initial run otherwise it will apply the incrementals and rolls forward the image copies of data files.
RMAN> run
{
sql 'alter system archive log current';
recover device type disk
copy of database with tag 'MIB_UPDATE';
backup incremental level 1
for recover of copy with tag 'MIB_UPDATE' database;
sql 'alter system archive log current';
}
list copy of database tag 'MIB_UPDATE';
list backup tag 'MIB_UPDATE';

2. Repeat the previous step for any future or subsequent backups.

SECTION 4 – Performing the Recovery from Incremental Merge (Updated) Backup

An unexpected outage has occurred and now a recovery is required to be performed to bring the database back up quickly as possible.

1. To perform the database recovery ensure that the database in MOUNT mode.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> shutdown abort
SQL> startup mount

2. In RMAN, switch database to use image copy and then perform recovery.
RMAN> set echo on;
switch database to copy;
run {
recover database;
alter database open;
}

3. Login to database and verify the location of data files are pointing to the recovered image copies.
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> col file_name form a75
SQL> select file_id, file_name from dba_data_files
/
FILE_ID FILE_NAME
---------- --------------------------------------------------
1 /u03/orafra/VLDB/datafile/o1_mf_system_6o7mj1gt_.dbf
2 /u03/orafra/VLDB/datafile/o1_mf_sysaux_6o7mj4j9_.dbf
3 /u03/orafra/VLDB/datafile/o1_mf_undotbs1_6o7ml4yx_.dbf
4 /u03/orafra/VLDB/datafile/o1_mf_users_6o7mlhl4_.dbf

SECTION 5 – Switching database files back to original data file location

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).

1. Backup the database in RMAN
RMAN> run
{
ALLOCATE CHANNEL d1 TYPE DISK FORMAT '/u02/oradata/VLDB/%U.dbf';
recover
copy of database with tag 'MIB_RELOCATE';
backup incremental level 1
for recover of copy with tag 'MIB_RELOCATE' database;
release channel d1;
}
list copy of database tag 'MIB_RELOCATE';
list backup tag 'MIB_RELOCATE';

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;
}

5. Login to database and verify the data files are pointing to the recovered image copies back in the original location (or directory).
[oracle@lnx01] export ORACLE_SID=VLDB; . oraenv
[oracle@lnx01] sqlplus / as sysdba
SQL> col file_name form a75
SQL> select file_id, file_name from dba_data_files
/
FILE_ID FILE_NAME
---------- --------------------------------------------------
1 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-SYSTEM_FNO-1_cim4edhc.dbf
2 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-SYSAUX_FNO-2_cjm4edig.dbf
3 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-UNDOTBS1_FNO-3_ckm4edjj.dbf
4 /u02/oradata/VLDB/data_D-VLDB_I-2643384069_TS-USERS_FNO-4_clm4edk2.dbf

CONSIDERATIONS
In conclusion, it is worth noting that when using RMAN Incremental Merge (Updated) Backup strategy, it is important to consider the following:

  • More storage is required to store the copy of database in addition to the traditional incremental backups.
  • For Point-In-Time recovery, continue to at least perform one full backup of database and include archive logs.  This can be can be performed less frequently (ie weekly, fortnightly, monthly).
  • Validate image copies and backups to detect if there is corruption or missing files which may compromise recovery.
  • Image copies of database should be on same storage tier as online data files to ensure performance it is not impacted when switching to database copy for recovery.
  • Image copies do not have to be stored in FRA, but should be stored on disk.

Enterprise Manager 10g – Useful Articles May 4, 2010

Posted by sosdba in Uncategorized.
Tags: , ,
add a comment

Back in the days of working in Oracle Global Support, I had written some articles which assisted in resolving common issues or enquiries relating to EM. Below are just a some of the many articles which I had written, which I thought I would share.

For further information on these articles please log in to My Oracle Support web portal:

Note.420504.1
Grid Control – How to Setup User Privileges and Administration for Database Targets

Note.460398.1
How to Rename Cluster Target for RAC from Default ‘crs’ in Grid Control ?

Note.431252.1
How To Discover Cluster Target Types in Grid Control

Note.418905.1
DB Targets – Configuring SPFILE

Note.429257.1
Setting Up Out of Bound (OOB) Notification – “Enterprise Manager is Down”

Note.435925.1
Out-of-Bounds Notification “Enterprise Manager Repository database is down. Error 1017”

Note.434694.1
Problem Listener Not Discovering In Grid Control

Note.429525.1
Troubleshooting EM Agent or EMCA installation for RAC and Non-RAC environments

Note.437660.1
Trying to access Grid Console / DBConsole / Metric Browser shows “Certificate Errors”

Welcome to SOS DBA May 3, 2010

Posted by sosdba in General.
Tags: ,
1 comment so far

I have been a DBA for over 9 years working at Telcos, Banking Institutions, Hosting companies and Oracle.

I never really thought about blogging about databases.  But I am thinking what the heck?! All these years of googling and stumbling across blogs (posts) that were able to assist me in keeping my databases intact, I thought its my time to contribute back to the community and share my experiences with others.  This blog will never be an “askTom.com” phenomenon but I hope it will be of use to someone out there wherever you are.  Remember that I am still that someone too!!

Tam Nguyen (aka SOSDBA)