Step Into Oracle DBA ( SID ): November 2013

Friday, November 29, 2013

Relocate a voting disk from ASM Diskgroup to a NFS shared file system and Vice Versa in 11g

##########################
## Objective
##########################

Relocate a voting disk from ASM Diskgroup to a NFS shared file system and Vice Versa in 11g

======================================================================================================================

Disk Group Where Voting Disk is now : +OCR_VOTE
New Voting disk location (NFS Mount Point ) : /u07/oracle

### Points to note

Voting Disk must be placed on shared devices; either on cluster filesystems, NFS or shared raw devices.

======================================================================================================================

Step 1 : Check the Present Location of Voting disk
Step 2 : Relocate voting disk from ASM to NFS shared Mount point
Step 3 : Now Check the New Location of Voting disk
Step 4 : Backup of Voting Disk
Step 5 : Relocate the Vote disk back to ASM Disk group
Step 6 : Now Check the New Location of Voting disk

======================================================================================================================

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Relocate voting Disk @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

======================================================================================================================
Step 1 :
--------
Check the Present Location of Voting disk
======================================================================================================================

crsctl query css votedisk

+ASM1 @ hostd01:/u01/home/oracle
> crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 19ac2decfd6c4f6ebf87cd9d247d7e7a (/dev/mapper/ocr01p1) [OCR_VOTE]
2. ONLINE 086dd7a7970e4f5dbf62d8760b8ab480 (/dev/mapper/ocr02p1) [OCR_VOTE]
3. ONLINE 4a96ec03cee14f54bfa8835a98c5d31b (/dev/mapper/ocr03p1) [OCR_VOTE]
Located 3 voting disk(s).
+ASM1 @ hostd01:/u01/home/oracle

======================================================================================================================
Step 2 :
--------
Relocate voting disk from ASM to NFS shared Mount point
======================================================================================================================

crsctl replace votedisk /u07/oracle/votedisk.dt

+ASM1 @ hostd01:/u07/oracle
> crsctl replace votedisk /u07/oracle/votedisk.dt
Now formatting voting disk: /u07/oracle/votedisk.dt.
CRS-4256: Updating the profile
Successful addition of voting disk 78e3c124a6f34f93bf2141cc31a97759.
Successful deletion of voting disk 19ac2decfd6c4f6ebf87cd9d247d7e7a.
Successful deletion of voting disk 086dd7a7970e4f5dbf62d8760b8ab480.
Successful deletion of voting disk 4a96ec03cee14f54bfa8835a98c5d31b.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
+ASM1 @ hostd01:/u07/oracle

Note : If you want to check the File system type, then in Linux use below command to find it.

df -T .

+ASM1 @ hostd01:/u07/oracle
> df -T .
Filesystem Type 1K-blocks Used Available Use% Mounted on
hostp02:/vol/u07/u07
nfs 637009920 400898560 236111360 63% /u07

======================================================================================================================
Step 3 :
--------
Now Check the New Location of Voting disk
======================================================================================================================

+ASM1 @ hostd01:/u07/oracle
> crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 78e3c124a6f34f93bf2141cc31a97759 (/u07/oracle/votedisk.dt) []
Located 1 voting disk(s).
+ASM1 @ hostd01:/u07/oracle

======================================================================================================================
Step 4 :
--------
Backup of Voting Disk ( We will take a backup of voting disk for safety purpose )
======================================================================================================================

dd if=/u07/oracle/votedisk.dt of=/u07/oracle/vd.bkp

+ASM1 @ hostd01:/u07/oracle
> dd if=/u07/oracle/votedisk.dt of=/u07/oracle/vd.bkp
41025+0 records in
41025+0 records out
21004800 bytes (21 MB) copied, 3.80843 seconds, 5.5 MB/s
+ASM1 @ hostd01:/u07/oracle

+ASM1 @ hostd01:/u07/oracle

-rw-r--r-- 1 oracle oinstall 21004800 Nov 29 03:15 vd.bkp
-rw-r----- 1 oracle oinstall 21004800 Nov 29 03:15 votedisk.dt

======================================================================================================================
Step 5 :
--------
Relocate the Vote disk back to ASM Disk group
======================================================================================================================

crsctl replace votedisk +OCR_VOTE

+ASM1 @ hostd01:/u07/oracle
> crsctl replace votedisk +OCR_VOTE
CRS-4256: Updating the profile
Successful addition of voting disk 02b990acc2414f21bf4d483e72ccf1ec.
Successful addition of voting disk 889aa822e04a4f1ebf648fccef8fbe69.
Successful addition of voting disk 0c28a49e64344f64bfe2ec1792305602.
Successful deletion of voting disk 78e3c124a6f34f93bf2141cc31a97759.
Successfully replaced voting disk group with +OCR_VOTE.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced

Thursday, November 28, 2013

Backup a Voting Disk in Oracle 11g & 10g Versions

##########################
## Objective
##########################

Backup a Voting Disk

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Voting Disk Backup @@@@@@@@@@@@@@
s
##########################
## From 11g R2 Onwards
##########################

Backing up voting disks manually is no longer required because voting disk data is backed up automatically in the OCR as part of any configuration change and voting disk data is automatically restored to any added voting disks.

##########################
## In 10g
##########################

======================================================================================================================

Step 1 : Backup Voting Disk
Step 2 : Find Voting disks in the Database

======================================================================================================================

======================================================================================================================
Step 1 :
--------
Find Voting disks in the Database
======================================================================================================================

Set crs environment,

crsctl query css votedisk

crs10g @ hostu03:/opt/oracle/admin
> crsctl query css votedisk
0. 0 /dev/raw/vot1
1. 0 /dev/raw/vot2
2. 0 /dev/raw/vot3

located 3 votedisk(s).
crs10g @ hostu03:/opt/oracle/admin

======================================================================================================================
Step 2 :
--------
Backup Voting Disk
======================================================================================================================

dd if=/dev/raw/vot1 of=/opt/oracle/admin/voting.bkp

crs10g @ hostu03:/opt/oracle/admin
> dd if=/dev/raw/vot1 of=/opt/oracle/admin/voting.bkp

280709+0 records in
280709+0 records out

crs10g @ hostu03:/opt/oracle/admin
> du -sh voting.bkp
137M voting.bkp
crs10g @ hostu03:/opt/oracle/admin

======================================================================================================================

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ END @@@@@

Tuesday, November 26, 2013

Enterprise Manager Agent Installation Pre Checks Utility for a sucessful Installation

We have built a new RAC Server for which agent installation needs to be done. So to confirm whether the installation of Agent will be successful.

I have performed the below steps which tells that installation of Agent will be successful.

########################
Command to Run Pre Checks
########################

cd /u01/app/oracle/exp/Software/agent/linux_x64/agent/install/

./runInstaller -prereqchecker PREREQ_CONFIG_LOCATION=/u01/app/oracle/exp/Software/agent/linux_x64/agent/stage/prereq -entrypoint "oracle.sysman.top.agent_Complete" -prereqLogLoc /tmp/agent_prereq_check -silent -waitForCompletion

Where,

Software Location - /u01/app/oracle/exp/Software/agent/

A Folder will be created in "/tmp/agent_prereq_check" which stores the logs and pre-requisties logs.

+ASM1 @ hostp16:/tmp/agent_prereq_check
> ls -ltr
total 16
drwxr-xr-x 2 oracle oinstall 4096 Nov 26 01:13 logs
-rw------- 1 oracle oinstall 0 Nov 26 01:13 prereq2013-11-26_01-13-39AM.err
drwxr-xr-x 2 oracle oinstall 4096 Nov 26 01:13 results
-rw------- 1 oracle oinstall 2516 Nov 26 01:13 prereq2013-11-26_01-13-39AM.out
-rw------- 1 oracle oinstall 3528 Nov 26 01:13 prereq2013-11-26_01-13-39AM.log
+ASM1 @ hostp16:/tmp/agent_prereq_check

########################
Pre Checks Log
########################

+ASM1 @ hostp16:/u01/home/oracle
> tage/prereq -entrypoint "oracle.sysman.top.agent_Complete" -prereqLogLoc /tmp/agent_prereq_chck.txt -silent -waitForCompletion <
Starting Oracle Prerequisite Checker...

Checking Temp space: must be greater than 150 MB. Actual 1404 MB Passed
Checking swap space: must be greater than 150 MB. Actual 32301 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-11-26_01-13-39AM. Please wait ...
Oracle Prerequisite Checker version Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Starting execution of Prerequisites...
Total No of checks: 6

Performing check for CertifiedVersions_agent
Checking if Oracle software certified on the current O/S...
Expected result: One of redhat-5.4,redhat-5.3,redhat-5.2,redhat-5.1,redhat-5,redhat-4,SuSE-11,SuSE-10,SuSE-9,SuSE-8,enterprise-5.4,enterprise-5.3,enterprise-5.2,enterprise-5.1,enterprise-5,enterprise-4,UnitedLinux-1.0,asianux-3,asianux-2,asianux-1
Actual Result: redhat-5
Check complete. The overall result of this check is: Passed

Check complete: Passed
========================================================
Performing check for Packages_agent
Checking for required packages installed on the system ....
Checking for make-3.81; found make-1:3.81-3.el5-x86_64. Passed
Checking for binutils-2.17.50.0.6; found binutils-2.17.50.0.6-20.el5_8.3-x86_64. Passed
Checking for gcc-4.1.1; found gcc-4.1.2-54.el5-x86_64. Passed
Checking for libstdc++-4.1.1; found libstdc++-4.1.2-54.el5-x86_64. Passed
Check complete. The overall result of this check is: Passed

Check complete: Passed
========================================================
Performing check for CompatibilityCheck
Checking whether the software compatibile for current o/s...
Check complete. The overall result of this check is: Passed

Check complete: Passed
========================================================
Performing check for AvailableTempSpace
Checking for sufficient diskspace in TEMP location...
Expected result: 400.0MB
Actual Result: 1224704MB
Check complete. The overall result of this check is: Passed

Check complete: Passed
========================================================
Performing check for AvailableInventorySpace
Checking for sufficient diskspace in Inventory location...
Expected result: 100.0MB
Actual Result: 38866944MB
Check complete. The overall result of this check is: Passed

Check complete: Passed
========================================================
Performing check for CheckHostName
Checking for the Hostname...
Check complete. The overall result of this check is: Passed

Check complete: Passed
========================================================
PrereqChecks complete

Thursday, November 21, 2013

Emctl Upload Agent Fails with OMS version not checked yet. Error

##########################
## Error
##########################

OMS version not checked yet.

### Full Error

no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin
> emctl upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors.

##########################
# Error Occurred
##########################

Error occured while trying to upload the Agent in 11.1.0.1.0 version of agent

##########################
## Command Executed
##########################

emctl upload agent

##########################
## Issue Description.
##########################

### 11.1.0.1.0

#########################################
# Happened When....
#########################################

Enterprise Manager Grid Agent was installed already on this server, and due to some situation, Agent software was de-installed and it was installed back.

After this Agent Upload to OMS is not working.

#########################################
# Upload of Agent
#########################################

no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin
> emctl upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors.

#########################################
# Status of Agent
#########################################

Agent is Up and Running but uploading is not happening.

no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin
> ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /u01/app/oracle/product/11.2/agent11g
Agent binaries : /u01/app/oracle/product/11.2/agent11g
Agent Process ID : 25235
Parent Process ID : 25200
Agent URL : https://hostp16:3872/emd/main/
Repository URL : https://oemp02:1159/em/upload
Started at : 2013-11-20 04:44:02
Started by user : oracle
Last Reload : 2013-11-20 04:44:02
Last successful upload : (none)
Last attempted upload : (none)
Total Megabytes of XML files uploaded so far : 0.00
Number of XML files pending upload : 27
Size of XML files pending upload(MB) : 5.23
Available disk space on upload filesystem : 66.16%
Last attempted heartbeat to OMS : 2013-11-20 04:49:26
Last successful heartbeat to OMS : unknown
---------------------------------------------------------------
Agent is Running and Ready
no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin

#########################################
# EM Agent Trace Info..
#########################################

no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/sysman/log
> vi emagent.trc
no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/sysman/log

2013-11-20 04:58:11,039 Thread-896039232 ERROR pingManager: Did not receive valid response to ping "ERROR-Agent is blocked.
Blocked reason is: Agent is out-of-sync with repository. This most likely means that the agent was reinstalled or recovered.
Please contact an EM administrator to unblock the agent by performing an agent resync from the console. Please contact EM adminstrator to unblock the agent"

##########################
## Solution
##########################

Login to EM Grid console,

Setup >> Agents >> hostp16 >>

Now Agent STatus will show as " Agent Unreachable, Agent is Blocked "

Click on "Upload Metric Data" from Upload TAB and it has given failure Message.

Now we have to do Resync of Agent,

Agent Resynchronization (Setup >> Agents >> hostp16 >> Click on Agent resynchronization),

click continue, Once it completes, again click "Upload Metric Data".

Now we can see Agent is uploading DAta's successfully.

no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin
> emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /u01/app/oracle/product/11.2/agent11g
Agent binaries : /u01/app/oracle/product/11.2/agent11g
Agent Process ID : 30627
Parent Process ID : 30594
Agent URL : https://hostp16:3872/emd/main/
Repository URL : https://oemp02:1159/em/upload
Started at : 2013-11-20 04:58:03
Started by user : oracle
Last Reload : 2013-11-20 05:06:11
Last successful upload : 2013-11-20 05:06:43
Total Megabytes of XML files uploaded so far : 0.93
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 66.18%
Last successful heartbeat to OMS : 2013-11-20 05:06:51
---------------------------------------------------------------
Agent is Running and Ready
no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin

no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin
> emctl upload agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
no_sid @ hostp16:/u01/app/oracle/product/11.2/agent11g/bin

How to find the Cluster Name in RAC Environment?

How to find the Cluster Name in RAC Environment?

Method 01 :
-------------

$ORA_CRS_HOME/bin/cemutlo -n

no_sid @ hostp18:/u01/app/oracle/product/11.2
> /u01/app/11.2/grid/bin/cemutlo -n
clusprod06
no_sid @ hostp18:/u01/app/oracle/product/11.2

Method 02 :
-------------

ls of $GRID_HOME/cdata will list out the Cluster Name. ( We will have directories called localhost, servername, and then cluster name )

no_sid @ hostorap16:/u01/app/11.2/grid/cdata
> ls -ltr
total 2924
drwxr-xr-x 2 oracle oinstall 4096 Nov 6 11:27 localhost
drwxr-xr-x 2 oracle oinstall 4096 Nov 6 11:47 hostorap16
drwxrwxr-x 2 oracle oinstall 4096 Nov 20 10:49 clusprod06

Friday, November 8, 2013

To Set SQL Plus Prompt to the database Name of which we were logged in using login.sql

########################
Task
########################

To Set SQL Plus Prompt to the database Name of which we were logged in.

Usually When we login to SQLPLUS, the prompt will be like below,

SQL>

And in many situations we need to check which database we are logged in. A Simple Solution is.

==================================================================================================
Step 1 : Use of login.sql
==================================================================================================

Locate login.sql file. The location for this file is $ORACLE_HOME/dbs

If the file is not available then create a file with the name login.sql and add the below content to it and save the file.

==================================================================================================
set termout off
spool set_prompt.sql
select 'set sqlprompt '||instance||'>'
from sys.v_$thread;
spool off
start set_prompt.sql
set termout on
==================================================================================================

==================================================================================================
Step 2 : Now login and Check
==================================================================================================

Now login to the database and check.

oralin @ exampled01:/u01/app/oracle/product/11.2/db_2/dbs
> sps

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 8 04:42:17 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> conn test/test@oradup as sysdba
Connected.
oradup>
oradup>conn test/test@orawin as sysdba
Connected.
orawin>
orawin>

==================================================================================================

Tuesday, November 5, 2013

ORA-12154: TNS: could not resolve the connect identifier specified

##########################
## Error
##########################

ORA-12154: TNS: could not resolve the connect identifier specified

### Full Error

Mon Nov 04 21:01:00 2013
Error 12154 received logging on to the standby

##########################
# Error Occurred
##########################

Error occured while Primary database is trying to connect to a standby database for transfering archive logs in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Primary trying to transfer Archive logs to Standby

##########################
## Issue Description / Troubleshooting
##########################

### 11.2.0.3.0

#########################################
# 1) Check Whether archive destination is showing any errors
#########################################

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

DEST_ID DEST_NAME TARGET NAME_SP DESTINATION STATUS ERROR DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
1 LOG_ARCHIVE_DEST_1 PRIMARY SYSTEM USE_DB_RECOVERY_FILE_DEST VALID NONE
2 LOG_ARCHIVE_DEST_2 STANDBY SYSTEM oralin_stdby ERROR ORA-12154: oralin_stdby
TNS:could not
resolve the
connect
identifier
specified

#########################################
# 2) TNS Names Used to Connect
#########################################

oralin_stdby.example.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kstdbylorap08)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oralin_stdby.example.com)
)
)

#########################################
# 3) Check Service & Domain parameters
#########################################

In standby :

SQL> sho parameter servi

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string oralin_stdby
db_domain string

##########################
## Solution
##########################

From Troubleshooting 2) output, we can see from TNSNAMES that service name is oralin_stdby and Domain is example.com

From Troubleshooting 2) output, Service name is correct but there is no domain set at the database level.

So the problem is with the domain name. I have removed Domain (example.com) from TNSNAMES and the connection to database was successful.

Modified TNSNAMES :

oralin_stdby.example.com =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kstdbylorap08)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oralin_stdby)
)
)

Step Into Oracle DBA ( SID )