Tag Archives: RAC

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

ASM, Oracle, Oracle 10g

CRS setup for ORACLE 10g ASM instance

Leave a comment

In order for ASM and RAC to work properly the CRS (Cluster Ready Service) must first be configured. This procedure outlines the configuration of CRS in a Linux environment.

1. Logon to Linux server as the root user.

2. Verify that the correct support packages are installed.

[root@mylinux_1 RPMS]# ls compat-libstdc++*
compat-libstdc++-296-2.96-132.7.2.i386.rpm

3. NOTE: depending on your OS you may require a different version or bit setting of this package. I am running x86_64 system so I will install the newest compat-listdc++ package for this system.

[root@mylinux_1 RPMS]# rpm -i compat-libstdc++-33-3.2.3-47.3.x86_64.rpm
[root@mylinux_1 RPMS]#

4. As the ROOT user navigate to the directory $ORACLE_HOME/bin. Execute the command ./localconfig add. The localconfig command cannot be run as the ORACLE user.

[root@mylinux_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

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.
CSS is active on these nodes.
mylinux_1
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@mylinux_1 bin]#

5. If CRS fails to install you must back out the files created with the command ./localconfig delete. Most likely the failure has occurred due to missing support packages. Verify that all packages suggested by Oracle Corporation for your OS and product version exist. You can find this listing at www.oracle.com/technology/index.html.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com