RAC Nodes Time Difference issue bcoz of NTP in 11gR2

In One of our client Time difference between the nodes were keep on increasing drastically.

I fed up by setting that manually between the nodes.

Finally Got a permanent Solution.

We have configured time synchronization using NTP network protocol. But late we have decided oracle to manage the time sync.

For this we have to stop NTP & enable CTSS thats it.. :)

CTSS - Cluster time synchronization Service.

 Time Synchronization – Steps

(Needs to be tested on a test Setup  -  Not tested)

1.       Stop the database

Oracle]$srvctl stop database –d erpdb –o immediate

2.Stop all cluster related services on both the nodes (stopping HAS would stop all the services)

Grid]$crsctl stop has

3.Stop ntp process and remove all the configuration files related to ntpd

# /sbin/service ntpd stop

#/sbin/service ntpd status

#chkconfig ntpd off

#mv  /etc/ntp.conf   /etc/ntp.conf_bkp

Remove the ntpd.pid file.

#rm  /var/run/ntpd.pid

Or  #mv /var/run/ntpd.pid /var/run/ntpd.pid_bkp

4.Now set the date by using date command in Linux by keeping 3 seconds difference between both Nodes

For Example:

Node1]date –s   12:00:00

Node]date –s  12:00:03

       5.As of Now ctss is in observer Mode.

       Reboot Both Nodes, Start high availability Service,

      6. Now check ctss status

      Grid]$crsctl check ctss

      It should be in Active Mode

      7.Start the database and related Services.

Creating a Manual Standby from RMAN Backup and switching it to primary

Please find the steps that I have followed,

1.Move Rman backup to Standby Server,

sql>startup nomount;

>rman target /

rman > restore standby controlfile from '/backup/rman/hhhhh';

rman> sql "alter database mount standby database"

rman > catalog backuppiece '/backup/rman/hhhh';

rman > catalog backuppiece '/backup/rman/hhhh';

run

{

set newname for datafile 4 to '/oradata1/db/data/users01.dbf';

set newname for datafile 3 to '/oradata1/db/data/undotbs01.dbf'; 

set newname for datafile 2 to '/oradata1/db/data/sysaux01.dbf';        

set newname for datafile 1 to '/oradata1/db/data/system01.dbf';

set newname for datafile 5 to '/oradata1/db/data/undotbs02.dbf';

set newname for datafile 6 to '/oradata1/db/data/indx_1.dbf';    

set newname for datafile 7 to '/oradata1/db/data/16k.dbf';

set newname for datafile 8 to '/oradata1/db/data/bs.dbf';

set newname for datafile 9 to '/oradata1/db/data/er.dbf';        

set newname for datafile 10 to '/oradata1 db/data/prod.dbf'; 

set newname for datafile 11 to '/oradata2/db/data/prod_2.dbf';

set newname for datafile 12 to '/oradata2/db/data/prod_3.dbf';

set newname for datafile 13 to '/oradata2/db/data/prod_4.dbf';

set newname for datafile 14 to '/oradata2/db/data/rman.dbf';

set newname for datafile 15 to '/oradata2/db/data/om.dbf';

set newname for datafile 16 to '/oradata2/db/data/om_1.dbf';

set newname for datafile 17 to '/oradata2/db/data/ys.dbf';

set newname for datafile 18 to '/oradata2/db/data/example.dbf';

set newname for datafile 19 to '/oradata2/db/data/prod_5.dbf';

set newname for datafile 20 to '/oradata2/db/data/01.dbf';

set newname for datafile 21 to '/oradata2/db/data/system02.dbf';

set until scn 2281756927;

Restore database;

Switch datafile all;

Recover database;

}

sql> Recover standby database;

2.To open the database,

sql> Alter database recover managed standby database finish force;

sql>Alter database commit to switchover to primary;

sql>alter database open;

(While opening the database, it will try to create redologs on the specified path in controlfile.

If that path physically does not exist, it will fail.. We can create new redolgs here by specifying rename command -SQL)

>lsnrctl start

Restoration of database from RMAN Backup

1.Install Oracle 10g(10.2.0.5) on RHEL 5.6(Required For My setup, You can install as per your Requirement

 2. Create user Oracle and set environmental variables

  export ORACLE_SID=TEST

               3.Create pfile with necessary parameters like  db_name  ,control_files,

                  Background_dump_dest

  4.Start the database in nomount Stage with the pfile.

  >Startup nomount

  5.Start RMAN and connect to the target instance

   $rman TARGET / NOCATALOG

   6.Restore the controlfile from backup piece.

                RMAN>Restore controlfile from ‘Path with backup piece Name’

                7.Catalog all the backup pieces

               RMAN>catalog backuppiece ‘Path with backup piece Name’

              Take controlfile trace and check whether all physical location for datafiles available,

              If not add set newname & switch datafile all commands to the below rman script by            

              Mentioning old path & New Path

              8.Restore and Recover.

              RMAN> run {

              2> allocate channel c1 type disk;

              3> restore database;

              4> recover database;

              5> release channel c1;

              6> }

               9. Open the database in Resetlogs option

              >Alter database open resetlogs;

Diskgroup addition 11gR2 Rac on Windows

Adding  Disk group on 2 - Node 11gr2 RAC on Windows

-->Control Panel\All Control Panel Items\Administrative Tools -->Computer Management --> Disk Management

Create a partition with required size from the Free Space.

In My setup Disk 1 is the shared storage between the Nodes. From the available 7.5 GB am going add one partition with 2GB of Size.

Dont assign drive letter.

.

Do not Format the Volume.

Refresh the Disk Management in other Node, So that the same will appear there too. In case if it has got some default drive letter in other Node, remove the drive letter.

Once the partitions are completed, I used the ASMTOOLG.EXE utility to stamp each partition with an ASM label so that Oracle can recognize these partitions as candidate disks for the ASM instance. I executed the ASMTOOLG.EXE program.

This step is mandatory, otherwise asmca utility will not identify your asmdisk.

When we add label, we could find the candidate device with 2GB of size as we created. Select the same disk and then We can add the prefix as our wish.

Once it is done Use asmca to create diskgroup using those disks.  --> Create Diskgroup

It will display the disk, which we have created. Since we have only one disk, I have used external redundancy.

Check the same in other node. Check whether the diskgroup mounted or not.

Its Done.

Issues Faced,

While trying to stamp asm header, found below error,

Error from Node 2. When I check the status of the diskgroup, it was unmounted. logged in to +ASM2 instance and then mounted those diskgroups,

>alter diskgroup dd2 mount;

Everything worked fine.

Online Redo corruption

Hi,

One of my development db was affected because of Online Redo corruption.

Since it is non - critical db I have applied this solution and got my db back.

If you face Online Redo corruption, Please raise SR with oracle before trying this soln.


DB would go upto Mount Stage.

You can fing LGWR error in alertlog file.

Soln.

Create a pfile

>create pfile from spfile;

In Pfile add the undocumented oracle parameter

_allow_resetlogs_corruption=true

then start the db using the edited Pfile.

Now It needs an fake recovery..

So

>startup Mount;

>recover database;

>alter database open resetlogs;

then give checpoint

>alter system checkpoint

>alter system switch logfile

recreate the corruputed redolog file by dropping the existing one.

Remove that undocumented parameter from pfile.

 

Then Found the database is not consistent.. and instance was terminated automatically..

 

In alertlog

 

Found ORA-600

 

The issue was because of some uncommited transactions (corruption) in the undo tablespace

 

So, created New undo tablespace and then assigned undo_tablespace into new undo tablespace, restarted the instance.

 

Finally Issue got cleared..

 

Experts comments welcome...

 

 

How to completely uninstall oracle 11g from windows server 2008

In 11g to uninstall oracle software, U have to manually run deinstall.bat

Its not possible thru OUI.

1. Log in as the Administrator.
2. Run  ---> Services.msc --> Stop all Oracle related Services
3. Run ---> From a cmd window, run <<ORACLE_HOME>>\deinstall\deinstall.bat.

It would prompt you for database, and it would take time to complete.

4. Delete the ORACLE_HOME directory.

In Windows server 2003, In HKLM under software u will find one oracle folder, Removing that in enough in server 2003,

Here We have to remove the following things from registry entry,

Run---> regedit--->  

HKLM\System\CurrentControlSet\Services\EventLog\Application\Oracle Services for MTS
HKLM\System\CurrentControlSet\Services\EventLog\Application\OracleDBConsole<SID>
HKLM\System\CurrentControlSet\Services\EventLog\Application\Oracle.<SID>
HKLM\System\CurrentControlSet\Services\EventLog\Application\Oracle.VSSWriter.<SID>
HKLM\CurrentControlSet\Services\Oracle11

HKLM - HKEY_LOCAL_MACHINE

then reboot the server once.

You are done with uninstallation.

Thanks.

Steps To Enable Auditing on RAC Database

Auditing has two types, there are

• standard - auditing based on statement, privilege and object-level
• fine-grained - auditing, which is based on content

Standard auditng has 3 levels.

statement - audit all actions on any type of object

privilege - audit actions that stem from system privileges

object-level - specific audit actions like select, update, insert or delete

Auditing depends on which value we pass on audit_trail

the values are..

• none - disables auditing
• os - records the information to a o/s file (uses parameter audit_file_dest for the file location)
• db - records the information in the database, use the view dba_audit_trail ( view accesses table sys.aud$ ) to display audit information.
• db, extended - as per the db value but also populates the sqlbind and sqltext clob columns
• xml - audits to the o/s file but in xml format (uses parameter audit_file_dest for the file location)
• xml,extended - as per xml option but also populates the sqlbind and sqltext clob columnstandard

For all 3 levels of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session).

Steps to enable auditing ...   Ex  11gR2 2 Node RAC setup.

We had to drop old audit (aud$) table, for that we need to keep our database in restricted mode.

Steps:

srvctl stop listener -n nodename1

srvctl stop listener -n nodename2

sqlplus>

alter system set cluster_database=false scope=spfile

alter system set audit_trail=db,extended scope=spfile

$srvctl stop database -d dbname -o immediate

>startup restrict

>truncate table aud$

>alter system set cluster_database=true scope=spfile

>shu immediate;

$srvctl start database -d dbname

$srvctl start listener -n nodename1

$srvctl start listener -n nodename2

create users

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

AUDIT ALL BY username BY ACCESS;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;

AUDIT EXECUTE PROCEDURE BY username BY ACCESS;