RAC (Real Application Cluster) some useful commands

Useful RAC Commands

Check if all nodes are up and running

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

$ CRS_HOME/bin/crs_stat -t

 

To jump to node 2

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

$ CRS_HOME/bin/crs_stat -t

How to start/stop nodes

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

CRS_HOME/bin/srvctl stop nodeapps -n rac1

CRS_HOME/bin/srvctl start nodeapps -n rac1

How to check Nodes status

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

$ srvctl status nodeapps -n rac1

$ ./crs_stat -p ora.myrac1.LISTENER_MYRAC1.lsnr

How to start all nodes and service

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

CRS_HOME/bin/crs_start -all

Login as “oracle” user (passward- oracle).

Open a terminal and execute the following commands:

Status of all instances and services

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

$ srvctl status database -d orcl

Instance orcl1 is running on node node1

Instance orcl2 is running on node ndoe2

Status of a single instance

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

$ srvctl status instance -d orcl -i orcl2

Instance orcl2 is running on node node2

Status of a named service globally across the database

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

$ srvctl status service -d orcl -s orcltest

Service orcltest is running on instance(s) orcl2, orcl1

Status of node applications on a particular node

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

$ srvctl status nodeapps -n node1

VIP is running on node: node1

GSD is running on node: node1

Listener is running on node: node1

ONS daemon is running on node: node1

Status of an ASM instance

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

$ srvctl status asm -n node1

ASM instance +ASM1 is running on node node1.

List all configured databases

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

$ srvctl config database orcl

Display configuration for our RAC database

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

$ srvctl config database -d orcl

node1 orcl1 /u01/app/oracle/product/10.1.0/db_1

node2 orcl2 /u01/app/oracle/product/10.1.0/db_1

Display all services for the specified cluster database

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

$ srvctl config service -d orcl

orcltest PREF: orcl2 orcl1 AVAIL:

Display the configuration for node applications - (VIP, GSD, ONS, Listener)

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

$ srvctl config nodeapps -n node1 -a -g -s -l

VIP exists.: /vip-linux1/192.168.101.5/255.255.255.0/eth0:eth1

GSD exists.

ONS daemon exists.

Listener exists.

Display the configuration for the ASM instance(s)

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

$ srvctl config asm -n node1

+ASM1 /u01/app/oracle/product/10.1.0/db_1

Stopping the Oracle10g RAC Environment

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

The first step is to stop the Oracle instance.

Once the instance (and related services) is down, then bring down the ASM instance.

Finally, shutdown the node applications (Virtual IP, GSD, TNS Listener, and ONS).

$ export ORACLE_SID=orcl1

$ lsnrctl stop

$ emctl stop dbconsole

$ srvctl stop instance -d orcl -i orcl1

$ srvctl stop asm -n node1

$ srvctl stop nodeapps -n node1

Starting the Oracle10g RAC Environment

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

The first step is to start the node applications (Virtual IP, GSD, TNS Listener, and ONS).Once the node applications are successfully started,then bring up the ASM instance. Finally, bring up the Oracle instance (and related services) and the Enterprise Manager Database console.

$ export ORACLE_SID=orcl1

$ lsnrctl start

$ srvctl start nodeapps -n node1

$ srvctl start asm -n node1

$ srvctl stop asm -n node1

$ srvctl start instance -d orcl -i orcl1

$ emctl start dbconsole

Start / Stop All Instances with SRVCTL

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

Start / Stop all of the instances and its enabled services. I just included this for fun as a way to bring down all instances!

$ srvctl start database -d orcl

$ srvctl stop database -d orcl

Check CSS/CRS health

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

# crsctl start crs (as root)

$ crsctl check crs

$ crsctl start resources

In the affected node, stop all services, login as root user, stop and start the crs

# /etc/init.d/init.crs stop

# /etc/init.d/init.crs start

How to check Listeners

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

$ crs_home/bin/crs_stat -v | grep LISTENER

$ crs_unregister ora.myrac2.LISTENER_MYRAC2_MYRAC2.lsnr (unregistering listener)

$ srvctl start listener -n rac2 (start listener)

Archivelog mode in Oracle database 11g

How to enable Archivelog mode in Oracle database 11g. 

There are two types of logging modes in Oracle database :-
1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.

2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.

First we will see on spfile how to enable ARCHIVE LOG MODE on SPFILE. 

How to check log mode :-

]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 21 23:03:44 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

----------

ORCL

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oraeng/apps/oracle/product/11.2.0/dbs
Oldest online log sequence     1
Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
--------- -    -----------
ORCL      NOARCHIVELOG

Currently the ORCL database is in NOARCHIVELOG mode

To change the Oracle database in ARCHIVELOG mode:-

1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> sho parameter spfile

NAME                           TYPE   VALUE

------------------------------ ---------  ----------------------------------------------------------------------

Spfile                            string    /oraeng/apps/oracle/product/11.2.0/dbs/spfileorcl.ora

SQL> alter system set log_archive_dest='/disk1/oradata/orcl/arch’ scope=spfile;

System altered.

2. After this you need to shut down your database and start again in MOUNT mode

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /disk1/oradata/orcl/arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------      ------------
ORCL      ARCHIVELOG
 

To change the Oracle database in NOARCHIVELOG mode.

 
1.Shutdown your running database.
 

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Start your database in MOUNT mode.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /disk1/oradata/orcl/arch

Oldest online log sequence     1

Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

---------      ------------

ORCL      NOARCHIVELOG

How to ENABLE ARCHIVELOG on pfile:-

]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 21 23:03:44 2015
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

----------

ORCLE

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /oraeng/apps/oracle/product/11.2.0/dbs
Oldest online log sequence     1
Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
--------- -    -----------
ORCL      NOARCHIVELOG

Currently the ORCL database is in NOARCHIVELOG mode

To change the Oracle database in ARCHIVELOG mode:-

1. If needed set the archive log destination where you want to save your archive logs whether to a single location . Then you have to set your archive location before changing your database to ARCHIVELOG mode.

SQL> sho parameter spfile

NAME                                    TYPE              VALUE

-------------------------  -----------------  ---------------------------------------------------------------------

Spfile                           string               /oraeng/apps/oracle/product/11.2.0/dbs/spfileorcl.ora

SQL> create pfile from spfile;

 

2. After this you need to shut down your database and start again in MOUNT mode

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

]$mkdir –p /disk1/oradata/orcl/arch

]$cd /oraeng/apps/oracle/product/11.2.0/dbs

Dbs]$ vi initorcl.ora

{“Add this parameter into to parameter file”}

Log_archive_dest=’/disk1/oradata/orcl/arch’

:wq

Dbs]$ rm –rf spfileorcl.ora

]$ sqlplus / as sysdba

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /disk1/oradata/orcl/arch

Oldest online log sequence     1

Current log sequence           1

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /disk1/oradata/orcl/arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------      ------------
ORCL      ARCHIVELOG

To change the Oracle database in NOARCHIVELOG mode.

1.Shutdown your running database.  

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2. Start your database in MOUNT mode.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1025298432 bytes

Fixed Size                  1341000 bytes

Variable Size             322963896 bytes

Database Buffers          696254464 bytes

Redo Buffers                4739072 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /disk1/oradata/orcl/arch

Oldest online log sequence     1

Current log sequence           1

SQL> select name,log_mode from v$database;

NAME      LOG_MODE

---------      ------------

ORCL      NOARCHIVELOG

I hope this POST helped you a lot.

Thank you.