http://oracle4ryou.blogspot.in/2013/11/oracle-11g-rac-interview-question-and.html?m=1
Thursday, 16 February 2017
How to Apply Critical Patch Update (CPU) on RAC
Applying CPU Patche "CPUJULY2011" for 10.2.0.4 Database on LINUX
Note:
Before continue the following step please read the README file from the patch.
Critical patch update (CPU) patches are cumulative, which means fixes from previous Oracle security alerts and critical patch updates are included.
It is not required to have previous security patches applied before applying the CPUJul2011 patches. However, you must be on the stated patch set level for a given product home before applying the CPUJul2011 patches for that release.
The Critical Patch Update (CPU) patches are rolling up gradation,
Do one of the following, depending on whether this is a RAC environment:
If this is a RAC environment, choose one of the patch installation methods provided by OPatch (rolling, all node, or minimum downtime), and shutdown instances and listeners as appropriate for the installation method selected.
This CPU patch is rolling RAC installable, Please refer to My Oracle Support Note 244241.1.
If this is not a RAC environment, shut down all instances and listeners associated with the Oracle home that you are updating.
Step by step CPU patches Applying:
If you are wish to applying rolling CPU patch on RAC, then the following steps must be following.
Rolling patch (no downtime)
-Shutdown the Oracle instance on node 1
-Apply the patch to the RAC home on node 1
-Start the Oracle instance on node 1
-Shutdown the Oracle instance on node 2
-Apply the patch to the RAC home on node 2
-Start the Oracle instance on node 2
-Shutdown the Oracle instance on node 3
-Apply the patch to the RAC home on node 3
-Start the Oracle instance on node 3
-Apply the patch to the RAC home on node 1
-Start the Oracle instance on node 1
-Shutdown the Oracle instance on node 2
-Apply the patch to the RAC home on node 2
-Start the Oracle instance on node 2
-Shutdown the Oracle instance on node 3
-Apply the patch to the RAC home on node 3
-Start the Oracle instance on node 3
1.Download the CPU patch p12419249_10204_Linux-x86from Metalink.
2.Change the owner of the patch file to oracle user.
# chown –R oracle: install p12419249_10204_Linux-x86.zip
3.Set the PATH variable to locate the opatch utility.
$ export PATH=$PATH: $ORACLE_HOME/OPatch
4.unzip the patch and go the unzipped directory
$unzip p12419249_10204_Linux-x86.zip
5.Fine the Opatch version
$ opatch version
Invoking OPatch 10.2.0.4.2
OPatch Version: 10.2.0.4.2
$ opatch lsinventory
Note: if you want check the CPU patch is whether rolling support or not, follow the steps.
-go to the patch directory
Cd /oracle/12419249
[oracle@rac1 12419249]$ opatch query -all
Invoking OPatch 10.2.0.4.2
Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/product/10.2.0/rdbms
Central Inventory : /oracle/product/10.2.0/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.2
OUI version : 10.2.0.4.0
OUI location : /oracle/product/10.2.0/rdbms/oui
Log file location : /oracle/product/10.2.0/rdbms/cfgtoollogs/opatch/opatch2011-05-01_08-55-20AM.log
--------------------------------------------------------------------------------
Patch created on 20 May 2011, 03:02:21 hrs PST8PDT
Need to shutdown Oracle instances: false (<--hear false mean we no need to down the database)
Patch is roll-backable: true
Patch is a rolling patch: true
Patch has sql related actions: false
Patch is an online patch: false
Patch is a portal patch: false
List of platforms supported:
46: Linux Intel
List of bugs to be fixed:
8534387: CPUJUL2009 DATABASE 10.2.0.4
8290506: CPUAPR2009 DATABASE 10.2.0.4
7375644: MLR BUG FOR 10.2.0.4 FOR CPUOCT2008
9352191: CPUAPR2010 DATABASE 10.2.0.4
9655017: CPUJUL2010 DATABASE 10.2.0.4
7150470: MLR BUG FOR 10.2.0.4 FOR CPUJUL2008
7592346: CPUJAN2009 DATABASE 10.2.0.4
9952272: CPUOCT2010 DATABASE 10.2.0.4
9119226: CPUJAN2010 DATABASE 10.2.0.4
11725015: CPUAPR2011 DATABASE 10.2.0.4
12419249: CPUJUL2011 DATABASE 10.2.0.4
8836308: CPUOCT2009 DATABASE 10.2.0.4
10249540: CPUJAN2011 DATABASE 10.2.0.4
List of optional components:
oracle.rdbms.rsf : 10.2.0.4.0
oracle.rdbms : 10.2.0.4.0
6.Backup the oraInventory and Opatch directory
$cp -R oraInventory old_oraInventory
$cp -R opatch old_opatch
7.If you are Applying on RAC, follow the below steps:
Shut down the instance one of node
$ srvctl stop instance -d racdb –i racdb1
Shut down the ASM instanc respected node
$ srvctl stop asm -n rac1
Shut down all Nodeapps services of the node
$ srvctl stop ndoeapps -n rac1
8.Go to the Patch Directory and invoke opatch apply.
$ cd 12419249
$opatch apply or $opatch napply -skip_subset -skip_duplicate
9.Verify Patches are applied
$opatch lsinventory -detail -oh $ORACLE_HOME
10.Now start the Node1 and repeat the same 1 to 10 steps on Node2
$ srvctl start nodeapps –n rac1
$srvctl start asm –n rac1
$srvctl start instance –d racdb –i racdb1
Note: if the database on rac1 located, now relocate to node2
$ crs_relocate ora.racdb.db
11.Now stop Instance,asm and nodeapps on node2
$ srvctl stop instance –d racdb –i racdb2
$ srvctl stop asm –n rac2
$ srvctl stop nodeapps –n rac2
12.Go to the Patch Direcotry and invoke the opatch apply on node2
$ cd 12419249
$ opatch apply or opatch napply -skip_subset -skip_duplicate
13.Verify Patches are applied
$opatch lsinventory -detail -oh $ORACLE_HOME
14.Start the Instance,Asm and Nodeapps on node2
$srvctl start instance –d racdb –i racdb2
$srvctl start asm –n rac2
$srvctl start nodeapps –n rac2
$crs_stat –t
Post CPU installation Steps:
For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus on each node.
Connect as SYSDBA and run the catbundle.sql script as follows:
On node1 and node2:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> @utlrp.sql
SQL> QUIT
The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series CPU.
For information about the catbundle.sql script, see My Oracle Support Note 605795.1 Introduction to Oracle Database catbundle.sql.
Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle for any errors:
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log
Recompiling Views in the Database
You may skip this section if you have recompiled views for this database during the installation of a previous CPU.
The time required to recompile the views and related objects depends on the total number of objects and on your system configuration.
In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time forview_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes.
If you want to check whether view recompilation has already been performed for the database, execute the following statement.
SELECT * FROM registry$history where ID = '6452863';
If the view recompilation has been performed, this statement returns one or more rows. If the view recompilation has not been performed, this statement returns no rows. If no rows returns then go the following steps.
The following steps recompile the views in the database. For a RAC environment, perform these steps on only one node.
1. Run the pre-check script (so named because it was initially released in CPUJan2008), which reports the maximum number of views and objects that may be recompiled:
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql
SQL> QUIT
The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.
Note:
If the database is not in a RAC environment(if Single Instance), perform this step and skip the next step 2. (If the database is in a RAC environment, go to the next step2.)
Run the view recompilation script, note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT
2.If the database is in a RAC environment, run the view recompilation script as follows, note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA. Stop all instances except the one where the view recompilation is being executed.
cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP NOMOUNT
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> @?/ cpu/view_recompile/view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP NOMOUNT;
Set the CLUSTER_DATABASE initialization parameter to TRUE:
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;
Restart the database:
SQL> QUIT
cd $CRS_HOME/bin
srvctl start database -d racdb
If any invalid objects were reported, run the utlrp.sql script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql
Then, manually recompile any invalid objects. For example:
SQL> alter package schemaname.packagename compile;
4. Verify Patches are applied.
$opatch lsinventory -detail -oh $CRS_HOME #if you have CRS_HOME
$opatch lsinventory -detail -oh $ORACLE_HOME #if you have both ORACLE_HOME
The CPU patch was successfully applied.
Wednesday, 15 February 2017
http://www.snapdba.com/2012/10/readable-df-h-output-in-hp-ux/#.WKSOx09MrIU
After my first encounter with a HP-UX system recently, I soon
discovered that annoyingly, neither the df -h or df -g commands work in HP-UX!
There are other commands such as df
-PK or bdf I’ve
found that can provide you with a more readable view of the
filesystems, but after a bit of faffing around, I put this together instead:
df -Pk | awk '{
if ( NR == 1 ) { next }
if ( NF == 6 ) { print }
if ( NF == 5 ) { next }
if ( NF == 1 ) {
getline record;
$0 = $0 record
print $0
}
}' | awk '
BEGIN {print
"Filesystem
Mount Point
Total GB Avail GB Used GB Used"
print
"--------------------------------------------- -------------------------
---------- ---------- ---------- -----"}
END {print ""}
/dev/ || /^[0-9a-zA-Z.]*:\// {
printf ("%-45.45s %-25s %10.2f %10.2f %10.2f
%4.0f%\n",$1,$6,$2/1024/1024,$4/1024/1024,$3/1024/1024,$5)
}'
Tuesday, 14 February 2017
Finding the location of the Oracle Alert Log 9i/10g/11g - updated for 12c
In this short post I'll try and see if we can provide a sensible way of establishing the location of the alert log for an oracle database, one that works for 9i, 10g, and 11g.
The Oracle Alert log tells us what major events have happened to the Oracle database.
In times gone by this was a nice text formatted file that most Oracle DBAs knew how to read. In 11g this was changed to an XML formatted file, with the advent of the ADR which is a first stab at automating the management of the large quantity of detailed trace file that Oracle manages.
We won't go into the detail of the ADR here except to note that this is where the alert log hangs out in recent versions of Oracle.
To cope with the backlash of complaints of people who didn't want to read an XML formatted alert log, Oracle continue to write the traditional form of the log as well simultaneously. This is perfect for our purposes.
So this post will try and establish some ways that you can find the alert log.
Some initial problems you may run into
1: The database is down - if it is down, then obviously Oracle cannot tell you what the location is as SQLPLUS is down.
2. There are multiple alert logs - in a RAC database for example there are several alert logs, one for each instance
3. you don't have access to the file location - being a database user is no guarantee of being able to access the alert log file location (even being DBA may not be sufficient ! - depends on the sandbox the sysadmins have you in)
However it breaks down into two basic cases
Database is Up or Database is Down
Database is Up
If the database is Up we can ask it where it is writing the alert Log
If you haven't bothered following an OFA architecture then you will probably be defaulted as per the manual
"Derived from the value of the
In this short post I'll try and see if we can provide a sensible way of establishing the location of the alert log for an oracle database, one that works for 9i, 10g, and 11g.
The Oracle Alert log tells us what major events have happened to the Oracle database.
In times gone by this was a nice text formatted file that most Oracle DBAs knew how to read. In 11g this was changed to an XML formatted file, with the advent of the ADR which is a first stab at automating the management of the large quantity of detailed trace file that Oracle manages.
We won't go into the detail of the ADR here except to note that this is where the alert log hangs out in recent versions of Oracle.
To cope with the backlash of complaints of people who didn't want to read an XML formatted alert log, Oracle continue to write the traditional form of the log as well simultaneously. This is perfect for our purposes.
So this post will try and establish some ways that you can find the alert log.
Some initial problems you may run into
1: The database is down - if it is down, then obviously Oracle cannot tell you what the location is as SQLPLUS is down.
2. There are multiple alert logs - in a RAC database for example there are several alert logs, one for each instance
3. you don't have access to the file location - being a database user is no guarantee of being able to access the alert log file location (even being DBA may not be sufficient ! - depends on the sandbox the sysadmins have you in)
However it breaks down into two basic cases
Database is Up or Database is Down
Database is Up
If the database is Up we can ask it where it is writing the alert Log
- Assumptions
- User has access to SQLPLUS
- User has access to v$parameter
- user can issue SHOW PARAMETER
The alert log location we want to see can be found in a variety of ways.
*** UPDATED - FOR 12c this doesn't work any more !!! ***
Read to the end for 12c answer.....
*** UPDATED - FOR 12c this doesn't work any more !!! ***
Read to the end for 12c answer.....
Show Parameter Background_DUMP_DEST
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/rep
o/REPO/trace
SQL>
So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g
Of course this isn't easy to capture into a variable so why not
SQL> set linesize 300
SQL> column value format a300
SQL> select value from v$parameter where name='background_dump_dest';
VALUE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/repo/REPO/trace
SQL>
So that is a location of the oracle alert log, works for all recent versions, 8i,9i,10g,11g
Which is a little easier.
Another way of getting pretty much the same thing is
( This one actually probes the ADR for the trace directory rather than looking at the legacy BDD - but only works in ADR databases - 11g and later )
SQL> select value from v$diag_info where name='Diag Trace';
VALUE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/repo/REPO/trace
SQL>
So that is a location of the oracle alert log, works for 11g
These techniques work when the database is started NOMOUNT,MOUNT or OPEN .. but they do not work, of course, when the database is down, as mentioned above.
Database is Down
OK if the db is down how do we find the alert log ?
There are a number of ways to find the alert log.
Simple : search the operating system directories for it.
The alert log will be named alert_SID.log where SID is the SID of the instance ( thus a rac DB may have many alert logs, and a Data Guard pair will have two
if we assume our SID=REPO then by the above convention the alert log will be named alert_REPO.log
We will not go into how to find the SID here, we assume this is well known.
so we know the name of the file how do we search for it.
Linux/Unix
[oracle@oracloud3 /]$ find / -name alert_REPO.log 2>/dev/null
/u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log
[oracle@oracloud3 /]$
We redirect STDERR to null because we don't want to read about all the stuff our Oracle account doesn't have access to.
Windows
Rather than do command line ( dir alert_repo.log /s ) just use the GUI to find the file, alternatively if that's not an option download and use something like Agent Ransack
OK so that's the simplistic method
What about a little more intelligence than that
If we were using an older database we used to be obliged to enter the BACKGROUND_DUMP_DEST into the initialization parameter file ( PFILE )... so we could go and find that and see if it's there.
For this we need ORACLE_HOME envar set.
On Linux/Unix
cd $ORACLE_HOME/dbs
on Windows
cd %ORACLE_HOME%/database
if a pfile is in use we will see it as initSID.ora, so following the above example it will be called initREPO.ora
we can then edit this using our favourite text editor to find the above variable and then we have the value.
OK ... but the current 11g, this doesn't work because
a) newer databases default to using an SPFILE ( binary version of the spfile )
b) The background dump dest variable is defaulted in 11g by the ADR so it does not need to be specified.
So in order to find the alert log we can try and put together a "where Oracle thinks it might put it " from analyzing the SPFILE.
In order to do this we take into account the following assumptions/axioms.
ADR Top level Directory is usually ORACLE_BASE/diag
RDBMS Logs are usually stored in ADR_base/rdbms
This is then broken down into DB_NAME/sid to take account of the fact that there may be a shared ADR directory tree across multiple instances of the same database ( i.e. RAC in some situations )
so we can complete the location of the alert log in 11g (Only )
as
ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace
or
/u01/app/oracle/diag/rdbms/repo/REPO/trace
as
ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace
or
/u01/app/oracle/diag/rdbms/repo/REPO/trace
This assumes a normally put-together database.
If you haven't bothered following an OFA architecture then you will probably be defaulted as per the manual
"Derived from the value of the
$ORACLE_BASE environment variable. If $ORACLE_BASE is not set, then derived fromORACLE_BASE as set by the Oracle Universal Installer. If ORACLE_BASE is not set, then $ORACLE_HOME/rdbms/log is the default location "
PS to find DB_NAME and ORACLE_BASE we may need to look at the spfile
this is difficult to view as it is a binary file so let's look at 'strings spfileREPO.ora' to get what we want.
Hope that helps .... several different ways to get what you need.
12c (12.1.0.2) answer : Background_dump_dest no longer useful as they have changed what that parameter is doing
$ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace is still useful to find the alert log
core_dump_dest can be used to get close
core_dump_dest = /u01/app/oracle/diag/rdbms/repo/REPO/cdump and then cd ../trace
It is an ongoing mystery why this is made difficult to find.
Here's a SQL script I wrote to find the alert log on Linux for 11g and 12c databases
set heading off pages 0 trimspool on lines 120 feedback off echo off
-- Chris Slattery November 2015
-- find the Alert Log Location in recent versions of Oracle 11g onwards
with DD (DDV) as (SELECT VALUE DDV FROM V$parameter WHERE NAME='diagnostic_dest'),
LCD (LCDV)as (select LOWER(value) LCDV FROM V$parameter WHERE NAME='db_name'),
INST (INSTV) as (select UPPER(value) INSTV FROM V$parameter WHERE NAME='instance_name')
select * from (select DDV||'/diag/rdbms/'||LCDV||'/'||INSTV||'/trace' from DD,LCD,INST ) where rownum= 1;
quit;
/
to run it
sqlplus -S "/ as sysdba" @get_alert_loc.sql
/u01/app/oracle/diag/rdbms/repo/REPO/trace
this is difficult to view as it is a binary file so let's look at 'strings spfileREPO.ora' to get what we want.
Hope that helps .... several different ways to get what you need.
12c (12.1.0.2) answer : Background_dump_dest no longer useful as they have changed what that parameter is doing
$ORACLE_BASE/diag/rdbms/DB_NAME/SID/trace is still useful to find the alert log
core_dump_dest can be used to get close
core_dump_dest = /u01/app/oracle/diag/rdbms/repo/REPO/cdump and then cd ../trace
It is an ongoing mystery why this is made difficult to find.
Here's a SQL script I wrote to find the alert log on Linux for 11g and 12c databases
set heading off pages 0 trimspool on lines 120 feedback off echo off
-- Chris Slattery November 2015
-- find the Alert Log Location in recent versions of Oracle 11g onwards
with DD (DDV) as (SELECT VALUE DDV FROM V$parameter WHERE NAME='diagnostic_dest'),
LCD (LCDV)as (select LOWER(value) LCDV FROM V$parameter WHERE NAME='db_name'),
INST (INSTV) as (select UPPER(value) INSTV FROM V$parameter WHERE NAME='instance_name')
select * from (select DDV||'/diag/rdbms/'||LCDV||'/'||INSTV||'/trace' from DD,LCD,INST ) where rownum= 1;
quit;
/
to run it
sqlplus -S "/ as sysdba" @get_alert_loc.sql
/u01/app/oracle/diag/rdbms/repo/REPO/trace
ls -lrt /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log
-rw-r-----. 1 oracle dba 164844 Nov 26 10:09 /u01/app/oracle/diag/rdbms/repo/REPO/trace/alert_REPO.log
so that's OK
Note will be different on Windows etc but this should cover the majority of cases. Yes I know I am making assumptions with Lower and Upper
Hope this helps people ..
http://askdba.org/weblog/2008/05/ora-1031-as-sysdba/
Solving ORA-1031 while connecting as “/ as sysdba” :
Many times we see an issue like this:
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
This is a very common and frequent error that can occur after the new oracle software install
or due to some permissions changes at OS level.
I will dicuss the approach to solve ORA-1031 error on UNIX environment.
1. Check that oracle_sid and oracle_home are set correctly as:
$ echo $ORACLE_SID
$ echo $ORACLE_HOME
Find the values returned by above command and match these values under /etc/oratab file, these
have to be listed there.
EXAMPLE:
========
$ echo $ORACLE_SID
BSNL
$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_2
$ cat /etc/oratab
BSNL:/u01/app/oracle/product/10.2.0/db_2:N
VSNL:/u01/app/oracle/product/10.2.0/db_2:N
The values above are matching with /etc/oratab entries
If the oracle_sid and oracle_home are not set properly then set it as:
$ export ORACLE_SID=BSNL
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2
And try to connect as "/ as sysdba" It should work now.
If these are correct but still the error is coming then move to step 2.
2. Ensure TWO_TASK is not set
$ echo $TWO_TASK
If it return any lines as:
TWO_TASK=
OR
TWO_TASK=<some_db_name>
Then unset the environment variable as:
$ unset TWO_TASK
Now try to connect as "/ as sysdba"
If these are correct but still the error is coming then move to step 3.
3.Check the permissions on the oracle executable file:
$ cd $ORACLE_HOME/bin
$ ls -la oracle
It should show the following permissions:
-rwsr-s--x 1 oracle oinstall 96725724 Apr 2 13:43 oracle
If its not the same then issue the following command to set the correct permissions:
$ chmod 6751 oracle
If these are correct but still the error is coming then move to step 4.
4. Check for the dba group at OS level. We need to make sure that Operating System users issuing / as sysdba belongs to dba group at OS level.
There is one file we need to check for this i.e $ORACLE_HOME/rdbms/lib/config.s OR $ORACLE_HOME/rdbms/lib/config.c (File name vary from OS to OS on some OS it is config.c and on some OS it is config.s). The value in these file is typically set to "dba" as:
.ascii "dba"
Login as oracle user:
# su - oracle
$ id
uid=111(oracle) gid=123(usdba)
Look for the gid value here.(usdba)
The gid value is usdba so we need to modify the config.c or config.s so that it should look like:
.ascii "usdba"
After making changes to config file relink oracle binaries as:
- Make sure that no oracle processes running
- Login as oracle
- Make sure LD_LIBRARY_PATH and ORACLE_HOME are set properly
$ORACLE_HOME/bin/relink all
If these are correct but still the error is coming then move to step 5.
5. Make sure that dba group at OS level only exists once in /etc/group file and that the users belonging to the dba group are properly comma separated.
Example:
usdba::123:oracle,oracle1
ii) Check that the oracle user uid and gid are same in /etc/group and /etc/passwd
If all these 5 settings are correct and still ora-1031 is coming then the only option is to take truss output and check while opening which file the error is coming.
e.g.
$ truss -aefo /tmp/truss.out sqlplus "/ as sysdba"
6. Ensure you are invoking sqlplus from correct ORACLE_HOME
This actually came as comment on this post and I would agree. Ensure that you are using sqlplus from correct ORACLE_HOME. To do this set
export PATH=$PATH:$ORACLE_HOME/bin
You can confirm the home using which sqlplus command
SQL> conn / as sysdba
ERROR:
ORA-01031: insufficient privileges
This is a very common and frequent error that can occur after the new oracle software install
or due to some permissions changes at OS level.
I will dicuss the approach to solve ORA-1031 error on UNIX environment.
1. Check that oracle_sid and oracle_home are set correctly as:
$ echo $ORACLE_SID
$ echo $ORACLE_HOME
Find the values returned by above command and match these values under /etc/oratab file, these
have to be listed there.
EXAMPLE:
========
$ echo $ORACLE_SID
BSNL
$ echo $ORACLE_HOME
/u01/app/oracle/product/10.2.0/db_2
$ cat /etc/oratab
BSNL:/u01/app/oracle/product/10.2.0/db_2:N
VSNL:/u01/app/oracle/product/10.2.0/db_2:N
The values above are matching with /etc/oratab entries
If the oracle_sid and oracle_home are not set properly then set it as:
$ export ORACLE_SID=BSNL
$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_2
And try to connect as "/ as sysdba" It should work now.
If these are correct but still the error is coming then move to step 2.
2. Ensure TWO_TASK is not set
$ echo $TWO_TASK
If it return any lines as:
TWO_TASK=
OR
TWO_TASK=<some_db_name>
Then unset the environment variable as:
$ unset TWO_TASK
Now try to connect as "/ as sysdba"
If these are correct but still the error is coming then move to step 3.
3.Check the permissions on the oracle executable file:
$ cd $ORACLE_HOME/bin
$ ls -la oracle
It should show the following permissions:
-rwsr-s--x 1 oracle oinstall 96725724 Apr 2 13:43 oracle
If its not the same then issue the following command to set the correct permissions:
$ chmod 6751 oracle
If these are correct but still the error is coming then move to step 4.
4. Check for the dba group at OS level. We need to make sure that Operating System users issuing / as sysdba belongs to dba group at OS level.
There is one file we need to check for this i.e $ORACLE_HOME/rdbms/lib/config.s OR $ORACLE_HOME/rdbms/lib/config.c (File name vary from OS to OS on some OS it is config.c and on some OS it is config.s). The value in these file is typically set to "dba" as:
.ascii "dba"
Login as oracle user:
# su - oracle
$ id
uid=111(oracle) gid=123(usdba)
Look for the gid value here.(usdba)
The gid value is usdba so we need to modify the config.c or config.s so that it should look like:
.ascii "usdba"
After making changes to config file relink oracle binaries as:
- Make sure that no oracle processes running
- Login as oracle
- Make sure LD_LIBRARY_PATH and ORACLE_HOME are set properly
$ORACLE_HOME/bin/relink all
If these are correct but still the error is coming then move to step 5.
5. Make sure that dba group at OS level only exists once in /etc/group file and that the users belonging to the dba group are properly comma separated.
Example:
usdba::123:oracle,oracle1
ii) Check that the oracle user uid and gid are same in /etc/group and /etc/passwd
If all these 5 settings are correct and still ora-1031 is coming then the only option is to take truss output and check while opening which file the error is coming.
e.g.
$ truss -aefo /tmp/truss.out sqlplus "/ as sysdba"
6. Ensure you are invoking sqlplus from correct ORACLE_HOME
This actually came as comment on this post and I would agree. Ensure that you are using sqlplus from correct ORACLE_HOME. To do this set
export PATH=$PATH:$ORACLE_HOME/bin
You can confirm the home using which sqlplus command
Monday, 13 February 2017
Clusterware startup sequence for Oracle 11g R2
Understanding how the clusterware startup occurs is critical to the diagnosis and resolution of Oracle RAC problems.
In Unix and Linux operating systems, there is a master daemon process named INIT that functions to start up additional system background processes. The INIT process first spawns the init.ohasd process, which in turn starts up the Oracle High Availability Services Daemon (OHASD). In turn, the OHASD daemon then spawns additional Clusterware processes at each startup level as shown next:
Level 1—OHASD spawns:
Cssdagent: Agent responsible for spawning CSSD
Orarootagent: Agent responsible for managing all root-owned ohasd resources
Oraagent: Agent responsible for managing all Oracle-owned ohasd resources
cssdmonitor: Monitors CSSD and node health (along wth the cssdagent)
Level 2—OHASD rootagent spawns:
Cluster Ready Services Daemon (CRSD)—primary daemon responsible for managing cluster resources
Cluster Time Synchronization Services Daemon (CTSSD)
Diskmon—provides disk monitoring services
ASM Cluster File System (ACFS) Drivers
During the second level of startup for Clusterware, the oraagent spawns the following Clusterware processes for 11g R2:
MDNSD: Used for DNS lookup
GIPCD: Used for inter-process and inter-node communication
GPNPD: Grid Plug and Play Profile Daemon
EVMD: Event Monitor Daemon
ASM: Resource for monitoring ASM instances
Level 3—CRSD spawns:
orarootagent: Agent responsible for managing all root-owned CRSD resources
oraagent: Agent responsible for managing all Oracle-owned CRSD resources
Level 4—CRSD rootagent spawns:
Network resource: To monitor the public network
SCAN VIP(s): Single Client Access Name Virtual IPs
Node VIPs: One per node
ACFS Registery: For mounting ASM Cluster File system
GNS VIP (optional): VIP for GNS
During this phase for Clusterware startup with 11g R2, the oraagent spawns the following processes:
ASM Resouce: ASM Instance(s) resource
Diskgroup: Used for managing/monitoring ASM diskgroups
DB Resource: Used for monitoring and managing the DB and instances
SCAN Listener: Listener for single client access name, listening on SCAN VIP
Listener: Node listener listening on the Node VIP
Services: Used for monitoring and managing services
ONS: Oracle Notification Service
eONS: Enhanced Oracle Notification Service
GSD: For 9i backward compatibility
GNS (optional): It is a grid naming service that performs name resolution
Log file locations for Oracle 11g RAC and ASM
The important Clusterware daemon logs are located under the
<GRID_HOME>/log/<nodename> directory. There are additional logfiles located under the <GRID_HOME>/log/<nodename>directory as listed next:
alert<NODENAME>.log - look here first for most clusterware issues
./admin:
./agent:
./agent/crsd:
./agent/crsd/oraagent_oracle:
./agent/crsd/ora_oc4j_type_oracle:
./agent/crsd/orarootagent_root:
./agent/ohasd:
./agent/ohasd/oraagent_oracle:
./agent/ohasd/oracssdagent_root:
./agent/ohasd/oracssdmonitor_root:
./agent/ohasd/orarootagent_root:
./client:
./crsd:
./cssd:
A daemon is a long-running background process that response for service requests. The term originated with Unix, but most operating systems use daemons in some form or another. In Unix, the names of daemons conventionally end in "d". Some examples include inetd , httpd , nfsd , sshd , named , and lpd .
---------
Don’t let this picture scare you too much. You aren’t responsible for managing all of these processes, that is the Clusterware’s job!
Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon). This daemon spawns 4 processe
Level 1: OHASD Spawns:
Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon). This daemon spawns 4 process.
Level 1: OHASD Spawns:
cssdagent – responsible for I/O fencing. Killing this process would cause node reboot. Stops,start and checks the status of occsd.bin daemon
orarootagent – Agent responsible for managing all root owned ohasd resources.
oraagent – Agent responsible for managing all oracle owned ohasd resources.
cssdmonitor – It monitors(via oclsomon functionality) OCSSD(it is nothing but cssd) process hangs and node hangs (via OPROCD funcationality) .
Level 2: OHASD rootagent spawns:
CRSD – This process is responsible for start, stop, monitor and failover of resource. It maintains OCR and also restarts the resources when the failure occurs.
CRS daemon has two modes of running
1) reboot mode -> in reboot mode , when crsd starts , all the resources are restarted.
2) restart mode -> in restart mode , when crsd starts , the resources are started as these were before the shutdown.
CTSSD – Cluster Time Synchronization Services Daemon - Provides Time Management in a cluster for Oracle Clusterware
Diskmon
ACFS (ASM Cluster File System) Drivers
Level 2: OHASD oraagent spawns:
MDNSD – Used by Grid Plug and Play to locate profiles in the cluster, as well as by GNS to perform name resolution. The mDNS process is a background process on Linux and UNIX and on Windows.
GIPCD – Used for inter-process and inter-node communication (A support daemon that enables Redundant Interconnect Usage.)
GPNPD – Provides access to the Grid Plug and Play profile, and coordinates updates to the profile among the nodes of the cluster to ensure that all of the nodes have the most recent profile.
EVMD – Distributes and communicates some cluster events to all of the cluster members so that they are aware of the cluster changes
ASM – Resource for monitoring ASM instances
Level 3: CRSD spawns:
orarootagent – A specialized oraagent process that helps crsd to manage resources owned by root, such as the network, and the Grid virtual IP address.
oraagent – Agent responsible for managing all oracle owned crsd resources.
Level 4: CRSD rootagent spawns:
Network resource – To monitor the public network
SCAN VIP(s) – Single Client Access Name Virtual IPs
Node VIPs – One per node
ACFS Registery – For mounting ASM Cluster File System
GNS VIP (optional) – VIP for GNS
Level 4: CRSD oraagent spawns:
ASM Resouce – ASM Instance(s) resource
Diskgroup – Used for managing/monitoring ASM diskgroups.
DB Resource – Used for monitoring and managing the DB and instances
SCAN Listener – Listener for single client access name, listening on SCAN VIP
Listener – Node listener listening on the Node VIP
Services – Used for monitoring and managing services
ONS – Oracle Notification Service
eONS – Enhanced Oracle Notification Service
GSD – For 9i backward compatibility
GNS (optional) – Handles requests sent by external DNS servers, performing name resolution for names defined by the cluster.
Understanding how the clusterware startup occurs is critical to the diagnosis and resolution of Oracle RAC problems.
In Unix and Linux operating systems, there is a master daemon process named INIT that functions to start up additional system background processes. The INIT process first spawns the init.ohasd process, which in turn starts up the Oracle High Availability Services Daemon (OHASD). In turn, the OHASD daemon then spawns additional Clusterware processes at each startup level as shown next:
Level 1—OHASD spawns:
Cssdagent: Agent responsible for spawning CSSD
Orarootagent: Agent responsible for managing all root-owned ohasd resources
Oraagent: Agent responsible for managing all Oracle-owned ohasd resources
cssdmonitor: Monitors CSSD and node health (along wth the cssdagent)
Level 2—OHASD rootagent spawns:
Cluster Ready Services Daemon (CRSD)—primary daemon responsible for managing cluster resources
Cluster Time Synchronization Services Daemon (CTSSD)
Diskmon—provides disk monitoring services
ASM Cluster File System (ACFS) Drivers
During the second level of startup for Clusterware, the oraagent spawns the following Clusterware processes for 11g R2:
MDNSD: Used for DNS lookup
GIPCD: Used for inter-process and inter-node communication
GPNPD: Grid Plug and Play Profile Daemon
EVMD: Event Monitor Daemon
ASM: Resource for monitoring ASM instances
Level 3—CRSD spawns:
orarootagent: Agent responsible for managing all root-owned CRSD resources
oraagent: Agent responsible for managing all Oracle-owned CRSD resources
Level 4—CRSD rootagent spawns:
Network resource: To monitor the public network
SCAN VIP(s): Single Client Access Name Virtual IPs
Node VIPs: One per node
ACFS Registery: For mounting ASM Cluster File system
GNS VIP (optional): VIP for GNS
During this phase for Clusterware startup with 11g R2, the oraagent spawns the following processes:
ASM Resouce: ASM Instance(s) resource
Diskgroup: Used for managing/monitoring ASM diskgroups
DB Resource: Used for monitoring and managing the DB and instances
SCAN Listener: Listener for single client access name, listening on SCAN VIP
Listener: Node listener listening on the Node VIP
Services: Used for monitoring and managing services
ONS: Oracle Notification Service
eONS: Enhanced Oracle Notification Service
GSD: For 9i backward compatibility
GNS (optional): It is a grid naming service that performs name resolution
Log file locations for Oracle 11g RAC and ASM
The important Clusterware daemon logs are located under the
<GRID_HOME>/log/<nodename> directory. There are additional logfiles located under the <GRID_HOME>/log/<nodename>directory as listed next:
alert<NODENAME>.log - look here first for most clusterware issues
./admin:
./agent:
./agent/crsd:
./agent/crsd/oraagent_oracle:
./agent/crsd/ora_oc4j_type_oracle:
./agent/crsd/orarootagent_root:
./agent/ohasd:
./agent/ohasd/oraagent_oracle:
./agent/ohasd/oracssdagent_root:
./agent/ohasd/oracssdmonitor_root:
./agent/ohasd/orarootagent_root:
./client:
./crsd:
./cssd:
A daemon is a long-running background process that response for service requests. The term originated with Unix, but most operating systems use daemons in some form or another. In Unix, the names of daemons conventionally end in "d". Some examples include inetd , httpd , nfsd , sshd , named , and lpd .
---------
Clusterware Startup Sequence
The following is the Clusterware startup sequence (image from the “Oracle Clusterware Administration and Deployment Guide):
Don’t let this picture scare you too much. You aren’t responsible for managing all of these processes, that is the Clusterware’s job!
Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon). This daemon spawns 4 processe
Level 1: OHASD Spawns:
- cssdagent – Agent responsible for spawning CSSD.
- orarootagent – Agent responsible for managing all root owned ohasd resources.
- oraagent – Agent responsible for managing all oracle owned ohasd resources.
- cssdmonitor – Monitors CSSD and node health (along wth the cssdagent).
Level 2: OHASD rootagent spawns:
- CRSD – Primary daemon responsible for managing cluster resources.
- CTSSD – Cluster Time Synchronization Services Daemon
- Diskmon
- ACFS (ASM Cluster File System) Drivers
Level 2: OHASD oraagent spawns:
- MDNSD – Used for DNS lookup
- GIPCD – Used for inter-process and inter-node communication
- GPNPD – Grid Plug & Play Profile Daemon
- EVMD – Event Monitor Daemon
- ASM – Resource for monitoring ASM instances
Level 3: CRSD spawns:
- orarootagent – Agent responsible for managing all root owned crsd resources.
- oraagent – Agent responsible for managing all oracle owned crsd resources.
Level 4: CRSD rootagent spawns:
- Network resource – To monitor the public network
- SCAN VIP(s) – Single Client Access Name Virtual IPs
- Node VIPs – One per node
- ACFS Registery – For mounting ASM Cluster File System
- GNS VIP (optional) – VIP for GNS
Level 4: CRSD oraagent spawns:
- ASM Resouce – ASM Instance(s) resource
- Diskgroup – Used for managing/monitoring ASM diskgroups.
- DB Resource – Used for monitoring and managing the DB and instances
- SCAN Listener – Listener for single client access name, listening on SCAN VIP
- Listener – Node listener listening on the Node VIP
- Services – Used for monitoring and managing services
- ONS – Oracle Notification Service
- eONS – Enhanced Oracle Notification Service
- GSD – For 9i backward compatibility
- GNS (optional) – Grid Naming Service – Performs name resolution
Short summary of the startup sequence: INIT spawns init.ohasd (with respawn) which in turn starts the OHASD process (Oracle High Availability Services Daemon). This daemon spawns 4 process.
Level 1: OHASD Spawns:
cssdagent – responsible for I/O fencing. Killing this process would cause node reboot. Stops,start and checks the status of occsd.bin daemon
orarootagent – Agent responsible for managing all root owned ohasd resources.
oraagent – Agent responsible for managing all oracle owned ohasd resources.
cssdmonitor – It monitors(via oclsomon functionality) OCSSD(it is nothing but cssd) process hangs and node hangs (via OPROCD funcationality) .
Level 2: OHASD rootagent spawns:
CRSD – This process is responsible for start, stop, monitor and failover of resource. It maintains OCR and also restarts the resources when the failure occurs.
CRS daemon has two modes of running
1) reboot mode -> in reboot mode , when crsd starts , all the resources are restarted.
2) restart mode -> in restart mode , when crsd starts , the resources are started as these were before the shutdown.
CTSSD – Cluster Time Synchronization Services Daemon - Provides Time Management in a cluster for Oracle Clusterware
Diskmon
ACFS (ASM Cluster File System) Drivers
Level 2: OHASD oraagent spawns:
MDNSD – Used by Grid Plug and Play to locate profiles in the cluster, as well as by GNS to perform name resolution. The mDNS process is a background process on Linux and UNIX and on Windows.
GIPCD – Used for inter-process and inter-node communication (A support daemon that enables Redundant Interconnect Usage.)
GPNPD – Provides access to the Grid Plug and Play profile, and coordinates updates to the profile among the nodes of the cluster to ensure that all of the nodes have the most recent profile.
EVMD – Distributes and communicates some cluster events to all of the cluster members so that they are aware of the cluster changes
ASM – Resource for monitoring ASM instances
Level 3: CRSD spawns:
orarootagent – A specialized oraagent process that helps crsd to manage resources owned by root, such as the network, and the Grid virtual IP address.
oraagent – Agent responsible for managing all oracle owned crsd resources.
Level 4: CRSD rootagent spawns:
Network resource – To monitor the public network
SCAN VIP(s) – Single Client Access Name Virtual IPs
Node VIPs – One per node
ACFS Registery – For mounting ASM Cluster File System
GNS VIP (optional) – VIP for GNS
Level 4: CRSD oraagent spawns:
ASM Resouce – ASM Instance(s) resource
Diskgroup – Used for managing/monitoring ASM diskgroups.
DB Resource – Used for monitoring and managing the DB and instances
SCAN Listener – Listener for single client access name, listening on SCAN VIP
Listener – Node listener listening on the Node VIP
Services – Used for monitoring and managing services
ONS – Oracle Notification Service
eONS – Enhanced Oracle Notification Service
GSD – For 9i backward compatibility
GNS (optional) – Handles requests sent by external DNS servers, performing name resolution for names defined by the cluster.
Subscribe to:
Posts (Atom)