Mani's Blog

March 31, 2011

iFly ( Indoor Sky diving)

Filed under: Thought of the day — mani @ 1:31 am
Tags: , , ,

We recently accomplished major projects DB Migration from Oracle to MySQL and Data center Migration. My company wanted to celebrate this event, so they took us to Indoor Sky diving. Initially we are given instruction on how to keep our body posture and Instructor’s signal to adjust the posture. We are given video instruction as well. I felt little bit excitement and nervous since it is my first time. Kits are given wear including helmet, suit, goggle and ear plug.

The fly area is covered by see through ( kind of tube). Instructor stays with you all the time, to make sure you are able to fly with correct balance. While you are flying you face look so funny with high pressure from the floor, which lifts you up in the air. With your posture and air pressure magic you can go up to 8 feet. If you are comfortable, instructor takes you up and helps you to spin. It was really fun. O My God!! the third time, the spin was really fast.

Wow!! It was wonderful experience. If you want to try. checkout http://www.iFlySFbay.com

Advertisements

March 25, 2011

>How to shutdown and startup Grid control server and Agent

Filed under: Oracle — mani @ 11:43 pm
Tags: , ,

>

Grid Control shutdown instruction

Use appropriate path name for the Agent and OMS.

1. Stop the Agent

export AGENT_HOME=/local/home/oracle/OracleHomes/agent10g;$AGENT_HOME/bin/emctl stop agent

2. Stop OMS service

export OMS_HOME=/local/opt/oracle/OracleHomes/oms10g;$OMS_HOME/opmn/bin/opmnctl stopall

3. Stop Listener

lsnrctl stop

4. Shutdown GRID control DB.

shutdown immediate

Grid Control startup instruction

Use appropriate path name for the Agent and OMS.

1. Startup GRID control DB.

startup

2. Start Listener

lsnrctl start

3. Start OMS service

export OMS_HOME=/local/opt/oracle/OracleHomes/oms10g$OMS_HOME/opmn/bin/opmnctl startall

4. Startup Agent

export AGENT_HOME=/local/home/oracle/OracleHomes/agent10g$AGENT_HOME/bin/emctl start agent

5. Validate the Grid Control is up

http://<hostname>.ningops.com:4889/em/console/home

March 24, 2011

>Migrating Oracle Database between ASM Disk groups

Filed under: Oracle — mani @ 12:03 am
Tags: , , , ,

>

We had our database on non-mirrored ASM disk group, we need to migrate database to mirrored ASM disk group.  Following steps are used to migrate.

Pre-Requisites : 

   1. Install Disk/storage and create ASM disks.

       /etc/init.d/oracleasm createdisk asm_ssd1 /dev/sdbd1

       /etc/init.d/oracleasm createdisk asm_ssd2 /dev/sdbe1

    2. Verify the ASM disks.

      /etc/init.d/oracleasm querydisk ASM_DATA01

       /etc/init.d/oracleasm querydisk ASM_DATA02

   3.  Create new diskgroup with redundancy .

        Example: CREATE DISKGROUP XN_DATA2 NORMAL REDUNDANCY DISK ‘/dev/oracleasm/disks/ASM_SSD1′,’/dev/oracleasm/disks/ASM_SSD2’   

Migration:

  1. Migrating datafile  
    
    shutdown immediate;startup mount;
    
    -- connect to RMAN
    
    rman
    
    connect target /
    
    copy datafile '+XN_DATA/xnb1rac/datafile/system.256.714364261' to '+XN_DATA2/xnb1rac/datafile/system.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/sysaux.263.714364261' to '+XN_DATA2/xnb1rac/datafile/sysaux.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/undotbs1.266.714364263' to '+XN_DATA2/xnb1rac/datafile/undotbs1.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/users.265.714364263' to '+XN_DATA2/xnb1rac/datafile/users.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/core.259.714367853' to '+XN_DATA2/xnb1rac/datafile/core.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_index.261.714367907' to '+XN_DATA2/xnb1rac/datafile/xn_index.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_msg.260.714367929' to '+XN_DATA2/xnb1rac/datafile/xn_msg.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_invite.264.714367945' to '+XN_DATA2/xnb1rac/datafile/xn_invite.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_lob.269.714367971' to '+XN_DATA2/xnb1rac/datafile/xn_lob.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/billing.270.714367985' to '+XN_DATA2/xnb1rac/datafile/billing.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_admin.271.714368001' to '+XN_DATA2/xnb1rac/datafile/xn_admin.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_sysm_nologging.272.714368019' to '+XN_DATA2/xnb1rac/datafile/xn_sysm_nologging.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_memb.273.714368047' to '+XN_DATA2/xnb1rac/datafile/xn_memb.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_msg.288.714439859' to '+XN_DATA2/xnb1rac/datafile/xn_msg3.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_adminjr.275.714368095' to '+XN_DATA2/xnb1rac/datafile/xn_adminjr.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_contact_data.276.714368129' to '+XN_DATA2/xnb1rac/datafile/xn_contact_data.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_contact_idx.277.714368149' to '+XN_DATA2/xnb1rac/datafile/xn_contact_idx.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_vg.278.714368167' to '+XN_DATA2/xnb1rac/datafile/xn_vg.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_pymt.279.714368181' to '+XN_DATA2/xnb1rac/datafile/xn_pymt.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/content_data.280.714368201' to '+XN_DATA2/xnb1rac/datafile/content_data.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/content_index.281.714368217' to '+XN_DATA2/xnb1rac/datafile/content_index.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/content_lob.282.714368229' to '+XN_DATA2/xnb1rac/datafile/content_lob.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_vg_idx.283.714368239' to '+XN_DATA2/xnb1rac/datafile/xn_vg_idx.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_pymt_idx.284.714368247' to '+XN_DATA2/xnb1rac/datafile/xn_pymt_idx.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/audit.285.714368285' to '+XN_DATA2/xnb1rac/datafile/audit.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_core.286.714415215' to '+XN_DATA2/xnb1rac/datafile/xn_core.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_opensocial.287.714415247' to '+XN_DATA2/xnb1rac/datafile/xn_opensocial.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_msg.289.714439909' to '+XN_DATA2/xnb1rac/datafile/xn_msg2.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_content_24k.290.714521183' to '+XN_DATA2/xnb1rac/datafile/xn_content_24k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_content_64k.292.714521329' to '+XN_DATA2/xnb1rac/datafile/xn_content_64k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_index_32k.293.716853111' to '+XN_DATA2/xnb1rac/datafile/xn_index_32k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_uniform_24k.294.714521479' to '+XN_DATA2/xnb1rac/datafile/xn_uniform_24k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_search.295.714521693' to '+XN_DATA2/xnb1rac/datafile/xn_search.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_subs_data.296.715291225' to '+XN_DATA2/xnb1rac/datafile/xn_subs_data.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_content.297.716963615' to '+XN_DATA2/xnb1rac/datafile/xn_content.dbf';
    
    -- From SQL Plus
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/system.256.714364261' to '+XN_DATA2/xnb1rac/datafile/system.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/sysaux.263.714364261' to '+XN_DATA2/xnb1rac/datafile/sysaux.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/undotbs1.266.714364263' to '+XN_DATA2/xnb1rac/datafile/undotbs1.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/users.265.714364263' to '+XN_DATA2/xnb1rac/datafile/users.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/core.259.714367853' to '+XN_DATA2/xnb1rac/datafile/core.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_index.261.714367907' to '+XN_DATA2/xnb1rac/datafile/xn_index.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_msg.260.714367929' to '+XN_DATA2/xnb1rac/datafile/xn_msg.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_invite.264.714367945' to '+XN_DATA2/xnb1rac/datafile/xn_invite.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_lob.269.714367971' to '+XN_DATA2/xnb1rac/datafile/xn_lob.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/billing.270.714367985' to '+XN_DATA2/xnb1rac/datafile/billing.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_admin.271.714368001' to '+XN_DATA2/xnb1rac/datafile/xn_admin.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_sysm_nologging.272.714368019' to '+XN_DATA2/xnb1rac/datafile/xn_sysm_nologging.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_memb.273.714368047' to '+XN_DATA2/xnb1rac/datafile/xn_memb.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_msg.288.714439859' to '+XN_DATA2/xnb1rac/datafile/xn_msg3.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_adminjr.275.714368095' to '+XN_DATA2/xnb1rac/datafile/xn_adminjr.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_contact_data.276.714368129' to '+XN_DATA2/xnb1rac/datafile/xn_contact_data.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_contact_idx.277.714368149' to '+XN_DATA2/xnb1rac/datafile/xn_contact_idx.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_vg.278.714368167' to '+XN_DATA2/xnb1rac/datafile/xn_vg.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_pymt.279.714368181' to '+XN_DATA2/xnb1rac/datafile/xn_pymt.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/content_data.280.714368201' to '+XN_DATA2/xnb1rac/datafile/content_data.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/content_index.281.714368217' to '+XN_DATA2/xnb1rac/datafile/content_index.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/content_lob.282.714368229' to '+XN_DATA2/xnb1rac/datafile/content_lob.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_vg_idx.283.714368239' to '+XN_DATA2/xnb1rac/datafile/xn_vg_idx.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_pymt_idx.284.714368247' to '+XN_DATA2/xnb1rac/datafile/xn_pymt_idx.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/audit.285.714368285' to '+XN_DATA2/xnb1rac/datafile/audit.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_core.286.714415215' to '+XN_DATA2/xnb1rac/datafile/xn_core.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_opensocial.287.714415247' to '+XN_DATA2/xnb1rac/datafile/xn_opensocial.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_msg.289.714439909' to '+XN_DATA2/xnb1rac/datafile/xn_msg2.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_content_24k.290.714521183' to '+XN_DATA2/xnb1rac/datafile/xn_content_24k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_content_64k.292.714521329' to '+XN_DATA2/xnb1rac/datafile/xn_content_64k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_index_32k.293.716853111' to '+XN_DATA2/xnb1rac/datafile/xn_index_32k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_uniform_24k.294.714521479' to '+XN_DATA2/xnb1rac/datafile/xn_uniform_24k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_search.295.714521693' to '+XN_DATA2/xnb1rac/datafile/xn_search.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_subs_data.296.715291225' to '+XN_DATA2/xnb1rac/datafile/xn_subs_data.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_content.297.716963615' to '+XN_DATA2/xnb1rac/datafile/xn_content.dbf';
  2. Validate Data file migration
    dba
    
    alter database open;
    
    select name from v$database;
    
    select name from v$datafile;
    
    shutdown immediate;
    
    startup
  3. Migrating tempfile
    alter tablespace temp add tempfile '+XN_DATA2' size 1G;
    
    alter database tempfile '+XN_DATA/xnb1rac/tempfile/temp.257.714364757' drop including datafiles;
    
    alter database tempfile '+XN_DATA/xnb1rac/tempfile/xn_temp.274.714416203' drop including datafiles;
    
    alter database tempfile '+XN_DATA/xnb1rac/tempfile/temp2.291.714521273' drop including datafiles;
    
    select * from v$tempfile;
  4. Migrating REDO Logfile
    select group#, status from v$log;
    
    ALTER DATABASE ADD LOGFILE GROUP 5 ( '+XN_DATA2') SIZE 204800K;
    
    ALTER DATABASE ADD LOGFILE GROUP 6 ( '+XN_DATA2') SIZE 204800K;
    
    ALTER DATABASE ADD LOGFILE GROUP 7 ( '+XN_DATA2') SIZE 204800K;
    
    ALTER DATABASE ADD LOGFILE GROUP 8 ( '+XN_DATA2') SIZE 204800K; 
    
    ALTER DATABASE drop logfile group 1;
    
    ALTER DATABASE drop logfile group 2;
    
    ALTER DATABASE drop logfile group 3;
    
    ALTER DATABASE drop logfile group 4;
    
    alter system checkpoint; 
    
    alter system switch logfile; 
    
    select member form v$logfile;
  5. Migrating Control file
    -- startup nomount;
    
    -- connect to RMAN
    
    connect target /
    
    restore controlfile to '+XN_DATA2/xnb1rac/controlfile/control01.ctl' from '+XN_DATA/xnb1rac/controlfile/current.262.714364561'; 
    
    restore controlfile to '+XN_DATA2/xnb1rac/controlfile/control02.ctl' from '+XN_DATA/xnb1rac/controlfile/current.262.714364561'; 
    
    restore controlfile to '+XN_DATA2/xnb1rac/controlfile/control03.ctl' from '+XN_DATA/xnb1rac/controlfile/current.262.714364561'; 
    
    -- connect to sqlplus 
    
    alter system set control_files='+XN_DATA2/xnb1rac/controlfile/control01.ctl',-'+XN_DATA2/xnb1rac/controlfile/control02.ctl',-'+XN_DATA2/xnb1rac/controlfile/control03.ctl' scope=spfile; 
    
    shutdown immediate;
    
    startup ;

March 23, 2011

>ORA-29701: unable to connect to Cluster Manager After converting RAC into single instance.

Filed under: Oracle — mani @ 2:07 pm
Tags: , ,

>ORA-29701: unable to connect to Cluster Manager  After converting RAC into single instance.

I have converted RAC into single instance, then all looked fine.  I have moved the database to new diskgroup in order to decommission the older SAN storage.  All went well, now all DB files are located on new diskgroup created on SSD storage.   When we unpublish the LUNS the server got rebooted and couldn’t start ASM instance successfully.

Tried following solutions, finally it started working.

[oracle@m120319 dbs]$ dba

SQL*Plus: Release 11.1.0.7.0 – Production on Mon Mar 14 18:17:50 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile=’/local/opt/oracle/product/11.1.0/db_1/dbs/init+ASM1.ora’;
ORA-29701: unable to connect to Cluster Manager
SQL>

1) Per recommendation from OTN changed the crs config as below, but didn’t help.

[root@m120319 ~]# /local/opt/oracle/product/11.1.0/db_1/bin/localconfig delete
Stopping Cluster Synchronization Services.
Unable to communicate with the Cluster Synchronization Services daemon.
Shutdown has begun. The daemons should exit soon.
Cleaning up Network socket directories
[root@m120319 ~]# /local/opt/oracle/product/11.1.0/db_1/bin/localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Configuration for local CSS has been initialized

Cleaning up Network socket directories
Setting up Network socket directories
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process…
Expecting the CRS daemons to be up within 600 seconds.
Giving up: Oracle CSS stack appears NOT to be running.
Oracle CSS service would not start as installed
Automatic Storage Management(ASM) cannot be used until Oracle CSS service is started
[root@m120319 ~]#

2) Did cleanup CRS as below and started ./init.cssd run &manually

a. Run the rootdelete.sh script then the rootdeinstall.sh script from the
$ORA_CRS_HOME/install directory on any nodes you are removing CRS from. Running
these scripts should be sufficent to clean up your CRS install. Rootdelete.sh
accepts options like nosharedvar/sharedvar, and nosharedhome/sharedhome. Make
yourself familiar with these options by reading the Oracle Clusterware and
Oracle Real Application Clusters Administration and Deployment Guide.
If you have any problems with these scripts please open a service request.
If for some reason you have to manually remove the install due to problems
with the scripts, continue to step 2:
b. Stop the Nodeapps on all nodes:
srvctl stop nodeapps -n
c. Prevent CRS from starting when the node boots. To do this issue the following as root:
rm /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab
d. If they are not already down, kill off EVM, CRS, and CSS processes or reboot
the node:
ps -ef | grep crs
kill
ps -ef | grep evm
kill
ps -ef | grep css
kill
Do not kill any OS processes, for example icssvr_daemon process !
e. If there is no other Oracle software running (like listeners, DB’s, etc…),
you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:
rm -f /var/tmp/.oracle/*
or
rm -f /tmp/.oracle/*

3) I had to start manually.

Finally restarted  as root:
# cd /etc/init.d

# ./init.cssd run &

4) Now ran the config again:
[root@m120319 oracle]# /local/opt/oracle/product/11.1.0/crs/bin/localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Configuration for local CSS has been initialized

Cleaning up Network socket directories
Setting up Network socket directories
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process…
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
m120319
Cluster Synchronization Services is active on all the nodes.
Oracle CSS service is installed and running under init(1M)
[root@m120319 oracle]#

Now I am able to start the ASM successfully.

[oracle@m120319 dbs]$ env | grep ORA
ORA_CRS_HOME=/local/opt/oracle/product/11.1.0/crs
ORACLE_SID=+ASM1
ORACLE_BASE=/local/opt/oracle
ORACLE_HOME=/local/opt/oracle/product/11.1.0/db_1
[oracle@m120319 dbs]$ dba

SQL*Plus: Release 11.1.0.7.0 – Production on Mon Mar 14 19:40:45 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup pfile=’/local/opt/oracle/product/11.1.0/db_1/dbs/init+ASM1.ora’
ASM instance started

Total System Global Area  283930624 bytes
Fixed Size            2158992 bytes
Variable Size          256605808 bytes
ASM Cache           25165824 bytes
ASM diskgroups mounted
SQL> select name ,state , free_mb, total_mb from v$asm_diskgroup;

NAME                   STATE          FREE_MB    TOTAL_MB
—————————— ———– ———- ———-
XN_DATA2               MOUNTED        17196      305250

SQL>

March 15, 2011

>How to cleanup trace, trm and audit files periodically

Filed under: Oracle — mani @ 10:34 pm
Tags: , , ,

>## Audit Cleanup
00 00 * * * /usr/bin/find /local/opt/oracle/product/11.1.0/db_1/rdbms/audit -name ora_\*.aud -mtime +2 -exec rm {} > /dev/null \; 2>&1

### Purge old tracefiles after 14 days.
###
20 00 * * * /usr/bin/find /local/opt/oracle/diag/rdbms/xnodb02b/XNODB02B1/trace -name \*.trc -mtime +14 -exec rm {} \; > /dev/null 2>&1
20 00 * * * /usr/bin/find /local/opt/oracle/diag/rdbms/xnodb02b/XNODB02B1/trace -name \*.trm -mtime +14 -exec rm {} \; > /dev/null 2>&1

>How to rotate alert log with retention

Filed under: Oracle — mani @ 10:30 pm
Tags: , ,

>Following script maintains alert 14 days, if you run the script once a day.  Generally I run it from cron

00 08 * * * /local/home/oracle/bin/rotate_alert.sh /local/opt/oracle/diag/rdbms/xnodb02b/XNODB02B1/trace/alert_XNODB02B1.log 14 > /dev/null 2>&1

#!/bin/sh

logfile=${1}
retention=${2:-14}

current=$retention
prev=`expr $current – 1`

while [ ${current} -gt 0 ]
do
if [ -f ${logfile}.${prev} ]
then
mv ${logfile}.$prev ${logfile}.$current
fi
current=`expr $current – 1`
prev=`expr $current – 1`
done

mv $logfile ${logfile}.0
touch $logfile

>How to kill SQL with specific wait event

Filed under: Oracle — mani @ 10:19 pm
Tags: ,

>In our DB instance, we happened to encounter “cursor: pin S wait on X” once in a while.  In our environment, it is okay to clear this session until we get a patch or solution from Oracle.  Schedule the script to run from the cron.

Following script was very useful, which kills that session automatically.

#!/bin/bash
# Oracle Environment settings
export ORA_CRS_HOME=/local/crs/oracle/product/11.1.0/crs
export ORACLE_SID=XNODB02B1
export ORACLE_BASE=/local/opt/oracle
export ORACLE_HOME=/local/opt/oracle/product/11.1.0/db_1
cat $HOME/dba/kill_pin_waits.log >> $HOME/dba/history_kill_pin_waits.log
$ORACLE_HOME/bin/sqlplus -s “/ as sysdba” << EOF > $HOME/dba/kill_pin_waits.log
connect / as sysdba
set echo off
set feedback off
set term off
set pagesize 0
spool /local/home/oracle/dba/pin_waits.sql
select ‘alter system kill session ‘||””||SID||’,’||SERIAL#||””||’ immediate;’  from v\$session where sid in (select sid from v\$session_wait where event like ‘cursor: pin S wait on X’);
spool off;
@/local/home/oracle/dba/pin_waits.sql
exit;
EOF
tmp=`wc -l $HOME/dba/kill_pin_waits.log|awk ‘{print $1}’`
if [ $tmp -ge 2 ]
then
echo “error occured”
cat $HOME/dba/kill_pin_waits.log | echo “library cache mutex occurred.”  | /bin/mailx -s “Cursor Pin Wait occurred” ‘nobody@nobody.com’
fi

>Convert Oracle RAC instance to Single Non-RAC instace.

Filed under: Oracle — mani @ 2:23 pm
Tags:

>

I was in a situation,  we need to get rid of SAN storage dependency on all our QA environment.  We had one DB environment with RAC setup.   I had to convert RAC instance into single instance and move the Database to SSD storage from SAN.

This post will brief you about steps to convert a RAC database to single instance.

I don’t think this method is supported by oracle as I was not able to find any document or metalink note ID which will provide the steps. So please DO NOT try this in production environment unless you have a conformation from Oracle Support.
However I tried the following steps in QA instance an it worked for me.1) Verify database running status on all nodes.
[oracle@m120319 ~]$ srvctl status database -d XNB1RAC
Instance XNB1RAC1 is running on node m120319
Instance XNB1RAC2 is running on node m12031a

2) Stop database using srvctl
[oracle@m120319 ~]$ srvctl stop database -d XNB1RAC
3) Remove the database entry from crs
[oracle@m120319 ~]$ srvctl remove instance -d XNB1RAC -i XNB1RAC2 
Remove instance XNB1RAC2 from the database XNB1RAC? (y/[n]) y 
[oracle@m120319 ~]$ srvctl remove instance -d XNB1RAC -i XNB1RAC1

Remove instance XNB1RAC1 from the database XNB1RAC? (y/[n]) y
4) Start the database on first instance
[oracle@m120319 ~]$ dba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 9 20:01:54 2011Copyright (c) 1982, 2008, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup 
ORACLE instance started.Total System Global Area 2.6724E+10 bytes
Fixed Size      2160272 bytesVariable Size   1.4764E+10 bytesDatabase Buffers  1.1811E+10 bytesRedo Buffers    146423808 bytesDatabase mounted.Database opened.SQL> alter system set cluster_database=false scope=spfile; 

System altered.

SQL> alter system set cluster_database_instances=1 scope=spfile; 

System altered.

SQL> alter database disable thread 2; 

Database altered.

5) Delete the unwanted thread and redo logfiles

SQL> select thread#, group# from v$log order by 1;

THREAD# GROUP#
———- ———-
1 5
1 6
1 18
1 16
1 14
1 13
1 10
2 12
2 15
2 11
2 17

THREAD# GROUP#
———- ———-
2 9
2 8
2 7
2 19

15 rows selected.

SQL> alter database drop logfile group 12;

Database altered.

SQL> alter database drop logfile group 15;

Database altered.

SQL> alter database drop logfile group 11;

Database altered.

SQL> alter database drop logfile group 17;

Database altered.

SQL> alter database drop logfile group 9
2 ;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 7
2 ;

Database altered.

SQL> alter database drop logfile group 19;

Database altered.

SQL> select thread#, group# from v$log order by 1;

THREAD# GROUP#
———- ———-
1 5
1 6
1 10
1 18
1 14
1 16
1 13

7 rows selected.

SQL> 6) Drop the unwanted undo tablespace

SQL> drop tablespace UNDOTBS2 including contents and datafiles; 

Tablespace dropped.
7) Create pfile from spfile
SQL> create pfile from spfile;

File created.

SQL> shut immediate ;Database closed.Database dismounted.ORACLE instance shut down.SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options[oracle@m120319 ~]$
8) Remove all references of second instance from the pfile and start the instance using pfile. Later you can again create spfile from pfile.

— removed all XNB1RAC2 references.

9) Startup the database and make sure all look good.
[oracle@m120319 dbs]$ dba

SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 9 20:09:28 2011

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 2.6724E+10 bytes
Fixed Size 2160272 bytes
Variable Size 1.4764E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 146423808 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>

March 1, 2011

>Oracle:Agent is Running but Not Ready

Filed under: Oracle — mani @ 1:12 am
Tags: ,

> My Oracle agent was not responding well.  I had to run following fixes to make it run.
1. Stop target agent host
<AGENT_HOME>/bin/emctl stop agent

2. On Verify no emagent process are running
ps -ef|grep emagent

3. Clean up old residue files
cd <AGENT_HOME>/sysman/emd
rm -rf agntstmp.txt
rm -rf lastupld.xml
rm -rf recv/*
rm -rf collection/*
rm -rf upload/*
rm -rf state/*

4. Secure Agent
<AGENT_HOME>/bin/emctl secure agent

5. Start Agent
<AGENT_HOME>/bin/emctl start agent

6. Set Clearstate for Agent
<AGENT_HOME>/bin/emctl clearstate agent

7. Upload data for agent
<AGENT_HOME>/bin/emctl upload agent

8. Run status for agent
<AGENT_HOME>/bin/emctl status agent

Create a free website or blog at WordPress.com.