Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Archive for March, 2013

What and Why CatBundle.sql ?

Posted by FatDBA on March 29, 2013

What is a Catbundle.sql script and it’s purpose after patching (Part of PSU Post-Patching) ?

Before asnwering about the role or importance of catbundle.sql script i would like to explain about what a database bundle is.
– A database bundle series is a sequence of patches where each patch in the series includes the contents of the previous patch in the series.

Installation of the database changes is done by catbundle.sql, which takes input from an XML file named bundledata_<bundle series>.xml.
catbundle.sql determines the last bundle applied to the database and executes only the scripts in the patch that have changed since the last bundle patch was applied.

Opatch tool replaces software files but does not modify database catalog. Applying a patch set update requires modifying the catalog. There is an sql script file named “catbundle.sql” under “$ORACLE_HOME/rdbms/admin” directory. Execute that script as sysdba to make necessary modification to the catalog.

* Patch your ORACLE_HOME before you start and execute the catbundle script.

Once done follow below provided steps if patching (PSU Track).

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

sql>@catbundle.sql psu apply

Once you’ve executed the catbundle.sql means your database is fully patched.

 

PrashAnt Dixit

Posted in Advanced | Tagged: | Leave a Comment »

Patching Explained

Posted by FatDBA on March 16, 2013

Patching
A patch is a piece of software designed to fix problems with, or update a computer program or its supporting data. This includes fixing security vulnerabilities and other bugs, and improving the usability or performance. Oracle Database suffers from these problems as well, hence patching is an important DBA activity that is performed by any administrator on a frequent basis. The method of applying patches on database is known as Patching.

Types of Patches:
Upgrade Patches:     Patches released by Oracle to upgrade databases. Like which takes your database from 10.2.0.1 to 10.2.0.4
CPU Patches:            Critical Patch Update, quarterly delivered to fix security issues.
PSU Patches:            PatchSet Update, also quarterly delivered, it includes CPU and a bunch of other one-off patches. First time introduced on 14th July 2009 by Oracle. Hence PSU = CPU + OneOff
One-Off Patches:     One-off patch is single fix patch, to fix a particular issue.

* Both CPU & PSU are released on a predictable time or schedule. specifically the Tuesday closest to the 15th of January, April, July,      and October.
* Although you can opt between PSU & CPU but it is always recommended that you choose CPU track.
* Once a PSU has been installed, the recommended way to get future security content is to apply subsequent PSUs. Reverting from PSU back to CPU, while possible, would require significant effort, and so is not advised.
Opatch is the built-in tool provided by Oracle. Resides in ORACLE_HOME/Opatch directory is the most trusted tool to apply almost all types of patches.

There are various Flags or options available in Opatch to apply patches in different scenarios.

-all_nodes: If want to apply/rollback/lsinventory (Check) on different nodes e.g in a RAC environment, use all_node option.
-force: If a conflict exist which prevents the patch from being applied, the -force flag can be used to apply the patch. OPatch will remove all the conflicting patches before applying the current patch.
-idfile: The input file which contains list of all patches seperated by commas or white spaces.

-invPtrLoc: used to locate orainst.loc file. In inventory oracle keep the detail of installed products in all oracle homes on the server, orainst.loc provides that list of installed products.
e.g  $ opatch lsinventory -InvPtrLoc /oracle/TEST/product/9.2.0/oraInst.loc

Contents of oraInst.loc file:
inventory_loc=/u01/app/oraInventory
inst_group=oinstall

-jre: This option tells Opatch to use JRE (java) from specified location instead of the default location under Oracle Home.
-local: Patch the local node, then update the inventory of the local node. Do not pass the patch or inventory update to other nodes.
-local_node:  This option can be used to specify to OPatch the local node name to be used for RAC mode application of the patch.
-no_inventory: Bypass the inventory for reading and updates.

– no_relink: option does not perform any ‘apply’ operations. This option can be used during multiple patch. This performs the linking step only once.
-no_sysmod: Option specifies not to update the files in the system. It just updates the inventory. It also not execute the pre and post scripts.

-phBaseFile: IF <patch_location> is not specified, use this option to point Optach to a file containing a list of patches to be n-applied.
-ocmrf: Give Opatch the absolute path to the OCM response file to be used for OCM configuration. OCM can collect the configuration of your servers and send it to Oracle support.  Oracle support staff will use it when you open a service request. With OCM, you’ll get better and faster response from Oracle support. You can also view your configuration on metalink. Oracle can suggest you updates or patches according to your configuration.Although it is useful, using OCM is not mandatory. You can still get support from Oracle without OCM. However, during patching, opatch utility may ask for an OCM response file.

To create OCMRF File — Opatch utility ships with a script file named “emocmrsp” which is used for creating a response file. “emocmrsp” script will ask your metalink account information and your internet connection information and store it in a response file named “ocm.rsp”.

-post: This option is used to pass parameters to the post script. This script is executed after application of the patch. The value for this option have to be enclosed in double quotes. The parameters will be common parameters which will be passed to post scripts of all patches being applied.
This option should be ended by option ‘opatch_post_end’.

-pre: This option is used to pass parameters to the pre script. This script is executed before application of the patch. The value for this option have to be enclosed in double quotes. The parameters will be common parameters which will be passed to pre scripts of all patches being applied. This option should be ended by option ‘opatch_pre_end’.

-report: Prints out the actions without executing.
-retry: Tells OPatch how many times it should retry in case of an inventory lock failure.
-runsql: This options commands OPatch to run sql scripts and procedures if they are present in the given patch.
-silent: This supresses any user inteeractions.
-sqlScript: Option used to specify the custom sql script to be run by OPatch after patching is completed.

-verbose: This option prints more OPatch output to the screen as well as to the log file.
-oh: Oracle Home  e.g. ./opatch lsinventory -oh /u01/app/grid/11.2.0.2/
-och: Oracle Clusterware Home (Path to CRS Home)

Command Description Syntax (Examples)
apply Installs an interim patch. The apply command applies an interim patch to a specified Oracle home. The ORACLE_HOME environment variable must be set to the Oracle home to be patched. opatch apply [-force] [-invPtrLoc (path)] [-oh] [-patch_location]
napply Installs n number of patches (hence napply). This command applies interim patches to several Oracle homes at the same time. opatch napply <patch_location> -id 1,2,3
auto Applies Oracle Clusterware patches. (For RAC systems)     —
lsinventory Lists what is currently installed on the system. opatch lsinventory -detail:                                                            opatch lsinventory -bugs_fixed asc:
query The query command queries a specific patch for specific details. It provides information about the patch and the system being patched. opatch query [-all] [patch_location] …
rollback The rollback command removes a specific interim patch from the appropriate Oracle home directory. opatch rollback -id (patch_id) [-ph (patch directory)]
version Prints the current version of the patch tool. opatch version

Posted in Advanced | Tagged: | Leave a Comment »

DBID (nid) Utility *A Powerfull Weapon in DBA’s Arsenal*

Posted by FatDBA on March 15, 2013

We should be thankful to that almighty that we are living an age where we have Oracle Database 10g and further releases available, If we’re not then changing some of the internal identifiers like DBNAME & DBID would be a real pain in the ass. Before Oracle 10g assigning a new DBNAME was only possible by manually re-creating the Control File and with no alternate or method to change DBID.

* DBNAME: Name of your database and can be comapred with your name e.g Prashant …
* DBID: Unique number (Identifier) assigned to your database in order to identify it among other DB’s and can be easily compared it with your house address. e.g 9/90-

DBNEWID (NID is same) is an oracle Database utlity/program which sits in $ORACLE_HOME/bin directory and can change the internal database identifier number or DBID and Database Name.

Alright, after that basic idea about DBNAME & DBID let’s jump to Oracle’s NID Utlity (Introduced in 10g).

DBID is a unique identifier for a database. During the years oracle released several versions of their database packages along with newer utilities and with many advancenments and re-imaged so many existing tools. RMAN (Recovery Manager) is one of the tool which along with time turned out to be a very powerful weapon in any DBA’a armoury for Backup and Recovery activities.
Up to 9i you could not register a Primary Database and a Clone Database in the same RMAN Repository or RMAN Catalog. DBID resolved this problem by changing DBID or DBNAME.

With NID Utility you can change:
– Only DBNAME
– Only DBID
– DBNAME and DBID Both

Points to be keep in mind when altering DBID of a database:
* After changing the DBID of database you must have to open your DB with RESETLOG option.
* After changing DBID of a database all old backups and archive logs becomes unsuable.
* Full Backup of your database soon after changing the DBID.

Points to be keep in mind when altering DBNAME of a database:
* This does not require to open database with RESETLOG option (No re-creation of online redo logs will happen & no sequence reset)
* Must change DB_NAME entry in your Parameter File to reflect new name.
* May have to create a new Password File.

Options available with NID Utility:

[oracle@localhost ~]$ nid help=y

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 17:39:20 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)
—————————————————-
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

CHANGING DBNAME & DBID Both

SQL> select name, dbid from v$database;

NAME            DBID
——— ———-
ORCL      1310899424

* Always recommended that you should take a full backup of your database before you go for DBID & DBNAME change.
– Mount your database after a graceful SHUTDOWN
– Change DBNAME & DBID
– SHUTDOWN IMMEDIATE
– STARTUP MOUNT (Could receive error like this: ORA-01103: database name ‘ANT’ in control file is not ‘ORCL’) ** Ignore
– Alter DB_NAME parameter in your pfile/spfile.
– SHUTDOWN IMMEDIATE
– Start your database with RESETLOGS option (SQL> alter database open resetlogs;)
– Verify both DBID & DBNAME.

[oracle@prashant ~]$ nid TARGET=sys/oracle90 DBNAME=ant
DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 13:04:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database ORCL (DBID=1310899424)
Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database ID and database name ORCL to ANT? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1310899424 to 124308932
Changing database name from ORCL to ANT
Control File /u01/app/oracle/oradata/orcl/control01.ctl – modified
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – modified
Datafile /u01/app/oracle/oradata/orcl/system01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/sysaux01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/undotbs01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/users01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/example01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/rmanrep/rman01.db – dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/orcl/temp01.db – dbid changed, wrote new name
Control File /u01/app/oracle/oradata/orcl/control01.ctl – dbid changed, wrote new name
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – dbid changed, wrote new name
Instance shut down

Database name changed to ANT.
Modify parameter file and generate a new password file before restarting.
Database ID for database ANT changed to 124308932.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

SQL> select name, dbid from v$database;

NAME            DBID
——— ———-
ANT        124308932

– Only DBNAME
To change DBNAME only you have to use both DBNAME and SETNAME parameters in nid string. DBNEWID performs validations in controlfiles headers only not datafiles. Change DB_NAME parameter soon after you modify DBNAME using nid utility in pfile/spfile.

Change DBNAME
SHUTDOWN IMMEDIATE
STARTUP MOUNT (Could recieve error like this: ORA-01103: database name ‘REDANT’ in control file is not ‘ANT’) ** Ignore
Alter DB_NAME parameter in your pfile/spfile.
SHUTDOWN IMMEDIATE
Verify both DBNAME.

[oracle@prashant ~]$ nid TARGET=sys/oracle90 DBNAME=redant SETNAME=YES

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 15 13:18:51 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database ANT (DBID=124308932)
Connected to server version 11.2.0

Control Files in database:
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Change database name of database ANT to REDANT? (Y/[N]) => Y

Proceeding with operation
Changing database name from ANT to REDANT
Control File /u01/app/oracle/oradata/orcl/control01.ctl – modified
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – modified
Datafile /u01/app/oracle/oradata/orcl/system01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/sysaux01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/undotbs01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/users01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/example01.db – wrote new name
Datafile /u01/app/oracle/oradata/rmanrep/rman01.db – wrote new name
Datafile /u01/app/oracle/oradata/orcl/temp01.db – wrote new name
Control File /u01/app/oracle/oradata/orcl/control01.ctl – wrote new name
Control File /u01/app/oracle/flash_recovery_area/orcl/control02.ctl – wrote new name
Instance shut down

Database name changed to REDANT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.

– Only DBID
If want to change DBID only, do not give DBNAME parameter in nid sting. This requires Opening of database with RESETLOG option.

[oracle@prashant ~]$ nid TARGET=SYS/oracle90
Connected to database REDANT (DBID=124308932)

Database ID for database REDANT changed to 629811920.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID – Completed succesfully.

To revert back changes use REVERT keyword.
[oracle@prashant ~]$ nid TARGET=SYS/oracle90 REVERT=YES

Posted in Advanced | Tagged: | Leave a Comment »

Questions ??

Posted by FatDBA on March 7, 2013

Let’s discover more about Data Guard 11g and discuss some of the topics out of the  ocean named Data Guard.

Query Scn in case of Active Data Guard ?
Fast-Start when using Maximum Performance Model … ?
Split-Brain Scenarios in DG 11g and how Fast-Start resolves the problem ?

Posted in Advanced | Tagged: , | Leave a Comment »

 
%d bloggers like this: