Category Archives: RAC

Oracle 10g, RAC, unix

Disabling Oracle RDBMS RAC features

Leave a comment

The determination of Oracle binary support for Real Application Clustering is made during installation on all supported platforms. Thus an installation made for a RAC database install will result in errors for single database instances. However, you can modify the binaries to support single instance database features after installation. This article details the steps to modify your Oracle binaries to support Oracle single instance database.

1. Logon to your Oracle database server as the Oracle software owner.

2. Change directory to your ORACLE_HOME/rdbms/lib.

cd $ORACLE_HOME/rdbms/lib

3. Use make to recreate your Oracle binaries with the option rac_off

make -f ins_rdbms.mk rac_off

4. Change directory to your ORACLE_HOME/bin.

5. Execute the utility relink with option all, to recreate all binary files.

relink all

6. This completes altering your Oracle RDBMS binaries to support Oracle RAC features.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

linux, Oracle 10g, RAC, unix

Enabling Oracle RDBMS RAC features

Leave a comment

The determination of Oracle binary support for Real Application Clustering is made during installation on all supported platforms. Thus, an installation made for a single instance database will not support creation of RAC by default. However, you can modify the binaries to support these features after installation. This article details the steps to modify your Oracle binaries to support Oracle RAC.

1. Logon to your Oracle database server as the Oracle software owner.

2. Change directory to your ORACLE_HOME/rdbms/lib.

cd $ORACLE_HOME/rdbms/lib

3. Use make to recreate your Oracle binaries with the option rac_on

make -f ins_rdbms.mk rac_on

4. Change directory to your ORACLE_HOME/bin.

5. Execute the utility relink with option all, to recreate all binary files.

relink all

6. This completes altering your Oracle RDBMS binaries to support Oracle RAC features.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

linux, Oracle, Oracle 10g, RAC, unix

Manually initializing Oracle OCFS2 stack

Leave a comment

Oracle provides the file system OCFS2 to support Oracle RAC file storage. This file system provides for a locking mechanism which allows files to be accessed by multiple Oracle instances while avoiding corruption. The OSFS2 file system binaries must be started before any OCFS2 formatted mount points can be accessed. This article shows the error generated when the OCFS2 stack has not been started and how to resolve the problem.

General OS error:


[root@mylinux /]# mount /dev/mapper/MPATH10 /u02
mount.ocfs2: Unable to access cluster service while trying initialize cluster

Resolution:

1. Logon to your server as root.
2. Execute the OS layer command /etc/init.d/o2cb enable

[root@mylinux /]# /etc/init.d/o2cb enable
Loading filesystem “configfs”: OK
Mounting configfs filesystem at /sys/kernel/config: OK
Loading filesystem “ocfs2_dlmfs”: OK
Mounting ocfs2_dlmfs filesystem at /dlm: OK
[root@mylinux /]#

3. Execute the OS layer command /etc/init.d/o2cb start

[root@mylinux /]# /etc/init.d/o2cb start
Starting O2CB cluster ocfs2: OK
[root@mylinux /]#

4. Attempt to mount your ocfs2 storage device.

[root@mylinux /]# mount /dev/mapper/MPATH10 /u02
[root@mylinux /]#

5. This completes restarting OCFS2 binaries.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, ASM, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, RAC, SQL, uncategorized, unix

Senior-Level DBA Interview Question:

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple of articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Senior Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions: NOTE: Answers here can be very broad, however they not always wrong. That is why it is important to have a proven DBA on staff or one you can call to review the answers. I have seen very inventive ways to get around the most impossible tasks and while they do not actually meet the standard way of doing the work, I would not count these people out. A person whom thinks outside of the box is very valuable in IT and believe it or not, some of the best DBA’s I have ever met thought completely outside the box, however always accomplished the task.

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

4. In what months does Oracle release CPU patches and what is the utility to apply them.

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

6. How do you logon to Oracle server without knowing any oracle passwords?

7. How would you determine that SCATTERED READ where occuring?

8. How would you see the SQL of the currently active sessions from a command prompt?

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Answers:

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

– Verify that all patches are installed and kernel parameters set.(Some not all SA say the system is ready when it is not. You can save yourself a lot of problems by double checking)
– Configuration of .profile or .bash_profile for the Oracle owner.
– Base Installation of Oracle (ie what to get to 10.2.0.4 must install Oracle 10.1 first).
– Upgrade to Oracle Version Desired.
– Upgrade the database(if upgrade is necessary)
– Run UTLRP.SQL.
– Apply lastest CPU patch.
– Run UTLRP.SQL.
– Configuration of Listener.
– Configuration of client TNSNAMES.ORA file.

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

Solution:
Relink all

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

Solution:
Differing Indian codes.

4. In what months does Oracle release CPU patches and what is the utility to apply them.

Solution:
JAN, APR, JUL, OCT

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

Solution:
Kill the smon process.

6. How do you logon to Oracle server without knowing any oracle passwords?

Solution:
Sudo to the Oracle software owners account and logon with the command sqlplus ‘/ as sysdba’

7. How would you determine that SCATTERED READ where?
Solution:

Look in the views v$session_wait or v$session_wait_history.

Could give:

Statspack or EM report.

8. How would you see the SQL of the currently active sessions from a command prompt?

Solution:
Compare tables V$SQL view by sql_address with V$SESSION view of currently active sessions.

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

Solution:
TNS_ADMIN

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Solution:
Other database would shutdown abort.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle 10g, RAC, SQL

Taking an Oracle database out of a multi-Node RAC

Leave a comment

I have found during some administrative tasks it is easier to have your system in single NODE configuration. An example of this, would be physical changes the database structure that due not include binary upgrades such as a CPU patch. It is easier to startup, shutdown, and restrict access thorough single stand alone system, rather then a RAC environment with several active nodes. I realize that most of you are quite verse in the user of the SRVCTL utility to administrate the instances and database. However, I would like to show the use of starting up the database in single node from within the database and using SRVCTL just to verify the status.

1. Logon to one node as sysdba.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 18:47:59 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” and as you can see we are attached to a two node RAC.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1
2 node2 PRIMARY_INSTANCE mylinux2

SQL>

3. Exit out of SQL*PLUS and use the SRVCTL utility to obtain the current RAC status. The utility SRVCTL verifies what we saw in the data dictionary view of GV$INSTANCE.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is running on node mylinux2

4. Due to the fact that we have two instance running, we will use the SRVCTL utility to shutdown the database.

mylinux1>$ srvctl stop database -d orcl

5. Again use the SRVCTL utility to view the status of your Instances and the results are displayed that both instance are shutdown.

srvctl status database -d orcl
Instance node1 is not running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

6. Now you do have a choice here: 1. You could startup the database and a single instance with the SRVCTL utility or 2. You could logon to SQL*PLUS like a non-RAC system and startup the instance and database. We will logon to SQL*PLUS as sysdba and issue the startup command.

mylinux1>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Sep 3 19:02:13 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1795162112 bytes
Fixed Size 1262332 bytes
Variable Size 587205892 bytes
Database Buffers 1191182336 bytes
Redo Buffers 15511552 bytes
Database mounted.
Database opened.
SQL>

7. Execute the following SQL “select inst_id, instance_name, instance_role, host_name from gv$instance;” to display the results of starting the database from NODE1.

SQL> select inst_id, instance_name, instance_role, host_name from gv$instance;

INST_ID INSTANCE_NAME INSTANCE_ROLE HOST_NAME
———- ————– —————– ——————
1 node1 PRIMARY_INSTANCE mylinux1

SQL>

8. You can verify these results with the SRVCTL utility.

mylinux1>$ srvctl status database -d orcl
Instance node1 is running on node mylinux1
Instance node2 is not running on node mylinux2
mylinux1>$

9. Your system is now executing in a SINGLE NODE fashion and you can began your administrative tasks.

Larry Catt OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, RAC

Oracle RAC and difference between V$ and GV$ views

Leave a comment

Since the introduction of Oracle RAC, we have seen the additional system dictionary views of GV$ which reflect information across the various nodes of a RAC system. Unlike the V$ views of the data dictionary, which relate to the current status of the single node you are connected to, the GV$ allows you to see status throughout the system. In this article we will show how to view sessions through the entire system instead of just a single node.

1. Connect to NODE1 of your Oracle RAC system as a DBA users, in this example we connect as the user ljcatt..

mylinux>$ sqlplus ljcatt/ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:05:43 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. Connect to NODE2 of your Oracle RAC as sysdba.

mylinux>$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Tue Sep 1 18:07:28 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

3. Use the SQL statement “select distinct(username) from v$session;” to view the current connections within NODE2 of your RAC. NOTE: You will not be able to see the connection from LJCATT in this dictionary view, because it only reflects connections to NODE2 and not the entire RAC.

SQL> select distinct(username) from v$session;

USERNAME
——————————

SYS

4. Wile still connected to NODE2 as sysdba, use the SQL statement “select distinct(username) from gv$session;” to view all sessions within the RAC. NOTE: Now you can see the connection to LJCATT.

SQL> select distinct(username) from gv$session;

USERNAME
——————————

LJCATT
SYS

SQL>

5. The GV$ dictionary views have the additional INST_ID column which give the node that the action is occurring in. Use the SQL statement “select inst_id from gv$session where username=’LJCATT’” to identify the NODE which LJCATT is currently connected to.

SQL> Select inst_id from gv$session where username=’LJCATT’;

INST_ID
———-
1

SQL>

The lesson learned, when you are administrating a RAC system use the dictionary views of GV$ and not V$.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com