Oracle RAC 11g (Real Application Cluster) CRSCTL commands

CRSCTL

The crsctl utility is used, along with the srvctl, to manage and monitor Oracle Clusterware resources and components. crsctl is stored under the GRID_HOME/bin directory and most crsctlcommands are only available to Clusterware administrators. You can use crsctl from any node in the cluster.  There are three different types of crsctl commands:

• Dual environment crsctl commands
• Oracle RAC environment crsctl commands
• Oracle Restart environment crsctl commands

---

Dual environment crsctl commands

The following lists the various crsctl commands and their functions are available for Oracle RAC and Oracle Restart environments:

• crsctl add [resource|type]
  • [resource] – Register resources to be managed by Clusterware. A resource might be an application process, a database, a service, a listener and so on.
  •  [type] – Create resource types
• Crcs check css
  • Check the status of Cluster Synchronization Services (CSS).
• Crsctl delete [resource|type]
  • [resource] – delete resources to be managed by Clusterware. A resource might be an application process, a database, a service, a listener and so on.
  • [type] – delete resource types
• Crsctl get hostname
  • Retrieves the host name of the local server
• Crsctl getperm [resource|type]
  • [resource] – Get user and group permissions for a specific resource.
  • [type] – Get permissions for a specific resource type.
• Crsctl modify[resource|type]
  • [resource] – Modify attributes of a specific Clusterware resource.
  • [type] – Modify attributes of a specific Clusterware type.
• Crsctl setperm [resource|type]
  • [resource] – Set permissions for a specific Clusterware resource.
  • [type] – Set permissions for a specific Clusterware type.
• Crsctl start resoruce
  • Start idle resources in the cluster.
• Crsctl status [resource|type]
  • [resource] – Determine the status of a specific Clusterware resource.
  • [type] – Determine the status of a specific Clusterware type.
• Crsctl stop resource
  • Stop a running resource.

---

Oracle RAC environment CRSCTL commands

The following lists the various crsctl commands and their functions which are available to manage the following components:

• Oracle Clusterware
• Oracle ASM
• CSS
• Cluster Time Synchronization Services

These commands are:

• Crsctl add [crs administrator|css votedisk|serverpool]
  • [crs administrator] – Add a user to the list of cluster administrators.
  • [css votedisk] – Add one or more voting disks to the cluster.
  • [serverpool] – Add a server pool to the cluster.
• Crsctl check [cluster|crs|resrouce|ctss]
  • [cluster] – Used to check the status of cluster. You can check the entire cluster or a specific node.
  • [crs] – Checks the status of Oracle HA Services and Clusterware on the local server.
  • [resource] – Check application specific agents assigned to a specific resource.
  • [ctss] – Check the status of the Cluster Time Synchronization services
• Crsctl config crs – Display the Oracle HA Services automatic startup configuration.
• Crsctl delete [crs administrator|css votedisk|node|serverpool]
  • [crs administrator] – Remove a crs administrator.
  • [css votedisk] – Remove a voting disk from the cluster.
  • [node] – Remove a node from the cluster.
  • [serverpool] – Remove a server pool from the cluster.
• Crsctl disable crs – Do not allow automatic startup of Oracle High Availability Services when the server boots up.
• Crsctl enable crs – Allow automatic startup of Oracle HA Services when the service boots up.
• Crsctl get [css|css ipmiaddr|nodename]
  • [css] – Get the value of a specific parameter related to css.
  • [css ipmiaddr] – get the address of the local IPMI device.
  • [nodename] – Get the node name of the local node.
• Crsctl getperm serverpool – Used to get permissions for a specific server pool.
• crsctl lsmodules – List components of modules available for debugging. You can list components for css, crs and evm.
• crsctl modify serverpool – Modify settings for a given server pool.
• crsctl pin css – Pin a node name with a specific node number.
• crsctl query crs [administrator|activeversion|releaseversion|softwareversion]
  • [administrator] – Display the list of users with Clusterware administrative privileges.
  • [activeversion] – Display the active version of Clusterware on the local node.
  • [releaseversion] – Display the release version of Clusterware.
  • [software version] – Display the software version on a specific server in the cluster.
• crsctl query css [ipnidevice|votedisk|
  • [ipnidevice] – Determine if a IPMI device driver is present on the node the command is run on.
  • [votedisk] – Display information on the voting disks.
• crsctl relocate [resource|server]
  • [resource] – Relocate resources to another server in the cluster.
  • [server] – Relocate a server to a different server pool.
• crsctl replace [discoverystring|votedisk]
  • [discoverystring] – Replace the existing discovery string used to locate the voting disk files.
  • [votedisk] – Move or replace the existing voting disks.
• crsctl set css – Set the value of a css parameter
• crsctl set css ipmiaddr – Store the address of the local IPMI device.
• crsctl set css ipmiadmin – Store the logon information of the IPMI administrator.
• crsctl setperm serverpool – Set permissions for a specific server pool.
• crsctl start [cluster|crs]
  • [cluster] – Start the Clusterware stack – Can be run from any node.
  • [crs] – Start Oracle HA Services on the local server.
• crsctl status [server|serverpool]
  • [server] – Display the status and configuration of one or more servers.
  • [serverpool] – Display the status and configuration of one or more server pools.
• crsctl stop [cluster|crs]
  • [cluster] – Stop the Oracle Clusterware stack on any node of the cluster.
  • [crs] – Stop the Oracle HA Services on the local server.
• crsctl unpin css – Unpin one or more servers.
• crsctl unset css – Restore a css parameter to its default value.

---

Oracle Restart environment CRSCTL commands

The following lists the various crsctl commands and their functions that are available to manage the Oracle High Availability Services (OHAS) stack. This stack includes:

• Oracle High Availability Services daemon (ohasd)
• Oracle ASM
• Cluster Synchronization Services (OSS) if ASM is installed

These commands only impact the cluster node that they are run on. The Oracle Restart Environment CRSCTL commands are:

• Crsctl check has – Check the status of ohasd
• Crsctl config has – Display the automatic startup configuration of OHAS on the server.
• Crsctl disable has – Disable OHAS services on the server when the server reboots.
• Crsctl enable has – Enable OHAS services on the server when the server reboots.
• Crsctl query has [releaseverions|softwareversion]
  • Releaseversions – Display the release version of the Oracle Clusterware software on the local node.
  • Softwareversion – Display the software version on the local node.
• Crsctl start has – Start OHAS on the local server.
• Crsctl stop has – Stop OHAS on the local server.

---

Examples of Most Commonly Used CRSCTL commands

This section provides some examples of the use of the crsctl command to manage Oracle Clusterware Version 11 Release 2.

Checking the Cluster

Use the crsctl command to determine if the cluster is working on a specific node as seen in this example:

$GRID_HOME/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

You can also use the crsctl command to check if CSS is running across all nodes of the cluster:

$GRID_HOME/bin/crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

The following crsctl command will provide a report on all the cluster resources and if they are running.

$GRID_HOME/bin/crsctl stat res –t
[oracle@rac1 oraagent_oracle]$ crsctl stat res -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE  ONLINE       rac1
ora.LISTENER.lsnr
ONLINE  ONLINE       rac1
ora.asm
ONLINE  ONLINE       rac1                  Started
ora.eons
ONLINE  ONLINE       rac1
ora.gsd
OFFLINE OFFLINE      rac1
ora.net1.network
ONLINE  ONLINE       rac1
ora.ons
ONLINE  ONLINE       rac1
ora.registry.acfs
ONLINE  ONLINE       rac1
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1        ONLINE  ONLINE       rac1
ora.oc4j
1        OFFLINE OFFLINE
ora.orcl.db
1        ONLINE  ONLINE       rac1                  Open
ora.repos.db
1        OFFLINE OFFLINE
ora.rac1.vip
1        ONLINE  ONLINE       rac1
ora.scan1.vip
1        ONLINE  ONLINE       rac1

In the output above you see the status of each resource (for example the listener, asm, the database, the scan listener and so on. You might also want to use the dig command to check that the addressed that are being used are correct. For example:

Dig @rac1.localhost

Starting and Stopping the Cluster

Use the crsctl command to start and stop the cluster on a specific node. This example starts the cluster and the following example stops the cluster.

$GRID_HOME/bin/crsctl start crs

$GRID_HOME/bin/crsctl stop crs

Enable or Disable Oracle Clusterware Daemons

Oracle Clusterware will automatically restart when a node is rebooted. There may be cases (like performing system maintenance) where you do not want clusterware to restart on a given node. The first command in the following example will keep Clusterware from stopping on the node that the command is executed on:. The second command in the following example will re-enable Clusterware on that node.

$GRID_HOME/bin/crsctl disable crs

$GRID_HOME/bin/crsctl enable crs

Managing the Voting Disks

The crsctl command is also useful for managing the voting disks. This command locates all the voting disks that you have allocated:

$GRID_HOME/bin/crsctl query css votedisk

You may want to add voting disks and the crsctl command is the way to do it. In this example we add a voting disk to a shared, non-ASM, disk system:

$GRID_HOME/bin/crsctl add css votedisk /ora05/shared/votedisk

You can add voting disks to ASM too, but the command syntax is a bit different as seen in this example where we use the crsctl command to add a voting disk to an ASM disk group called +VOTEDISK_DG:

$GRID_HOME/bin/crsctl replace votedisk +VOTEDISK_DG

We can also remove voting disks as seen in this example where we remove the voting disk we previously created (yes, we are a bit fickle!). Again, this is for non ASM filesystems:

$GRID_HOME/bin/crsctl delete css votedisk /ora05/shared/votedisk

You would delete a voting disk by using the voting disk GUID and the crsctl command as seen here:

$GRID_HOME/bin/crsctl delete css votedisk voting-disk-GUID

These commands can be executed without taking down the cluster and are very helpful when migrating voting disk to/from ASM.  You can also move voting disks from one location to another using the crsctl command as seen here:

$GRID_HOME/bin/crsctl replace votedisk {+VOTEDISK_DG}

Adding a Cluster Administrator

You can use the crsctl command to add a cluster administrator as seen in this example:

Crsctl add crs administrator –u new_user_name

Removing a Cluster Administrator

You can use the crsctl command to add a cluster administrator as seen in this example:

Crsctl delete crs administrator –u remove_user_name

Note that when enabling role-separate management, you will need to remove the * value as seen here:

Crsctl delete crs administrator –u ”*”

Listing Cluster Administrators

To list the users that are cluster administrators use the crsctl command as seen here:

Crsctl query crs administrator

Relocate a Resource

Use the crsctl command if you have a resource on one host that you need to locate to another host. For example, if you wanted to locate a resource called delta_one from host rac1 to host rac2 then you would issue the following command:

$GRID_HOME/bin/crsctl relocate resource delta_one –n rac1 –s rac2

How does the METHOD_OPT parameter work?

Once we addresse the most popular question surrounding statistics gathering, what sample size should be used? Once that decision has been made, folks typically turn their attention to histograms and the time honored question of, do I need histograms or not?
Regardless of which side of this religious debate you come down on, you are going to need to figure out what to set the METHOD_OPT parameter to.
This post explains in detail what the METHOD_OPT parameter controls and how it can be used to influence which columns get statistics and what type of statistics they get.

The METHOD_OPT parameter is probably the most misunderstood parameter in the DBMS_STATS.GATHER_*_STATS procedures. It’s most commonly known as the parameter that controls the creation of histograms but it actually does so much more than that. The METHOD_OPT parameter actually controls the following,

• which columns will or will not have base column statistics gathered on them
• the histogram creation,
• the creation of extended statistics

The METHOD_OPT parameter syntax is made up of multiple parts. The first two parts are mandatory and are broken down in the diagram below.

The leading part of the METHOD_OPT syntax controls which columns will have base column statistics (min, max, NDV, number of nulls, etc) gathered on them. The default, FOR ALL COLUMNS, will collects base column statistics for all of the columns (including hidden columns) in the table.  The alternative values limit the collection of base column statistics as follows;

FOR ALL INDEXED COLUMNS limits base column gathering to only those columns that are included in an index. This value is not recommended as it is highly unlikely that only index columns will be used in the select list, where clause predicates, and group by clause of all of the SQL statement executed in the environment.

FOR ALL HIDDEN COLUMNS limits base column statistics gathering to only the virtual columns that have been created on a table. This means none of the actual columns in the table will have any column statistics gathered on them. Again this value is not recommended for general statistics gathering purposes. It should only be used when statistics on the base table columns are accurate and a new virtual column(s) has been created (e.g. a new column group is created). Then gathering statistics in this mode will gather statistics on the new virtual columns without re-gathering statistics on the base columns.
Note that if a column is not included in the list to have statistics gathered on it, then only its average column length is gathered. The average column length is used to correctly compute average row length and discarded (i.e., not saved to disk) after use.

The SIZE part of the METHOD_OPT syntax controls the creation of histograms and can have the following settings;

AUTO means Oracle will automatically determines the columns that need histograms based on the column usage information (SYS.COL_USAGE$), and the presence of a data skew.

An integer value indicates that a histogram will be created with at most the specified number of buckets. Must be in the range [1,254]. To force histogram creation it is recommend that the number of buckets be left at 254. Note SIZE 1 means no histogram will be created.

REPEAT ensures a histogram will only be created for any column that already has one. If the table is a partitioned table, repeat ensures a histogram will be created for a column that already has one on the global level.  However, this is not a recommended setting, as the number of buckets currently in each histogram will limit the maximum number of buckets used for the newly created histograms. Lets assume there are 5 buckets currently in a histogram. When the histogram is re-gathered with SIZE REPEAT, the newly created histogram will use at most 5 buckets and may not been of good quality.  
SKEWONLY automatically creates a histogram on any column that shows a skew in its data distribution.

If the default value of METHOD_OPT parameter, FOR ALL COLUMNS SIZE AUTO, doesn’t work in your particular environment then you most likely fall into one of the following categories,

• Automatically create a histogram on any column in the table except a specific column
• Only create a histogram on this specific column(s)

In an earlier blog post we showed an example of how to prevent Oracle from creating a histogram on a specific column, so let’s look at option 2, creating a histogram only on specific columns.  Let’s assume we only wanted to create a histogram on the CUST_ID of the SALES table. Remember, the METHOD_OPT parameter controls which columns get basic statistics as well as which columns get histograms, so we need to think about setting the METHOD_OPT parameter in two parts.

The first part will specify which columns will have base statistics gathered on them. In this case we wanted all of the columns in the table to have base statistics so we should use FOR ALL COLUMNS.  But what about the SIZE part of the parameter? We only want a histogram on one column, so for this leading edge of the parameter setting we need to specify SIZE 1 to prevent a histogram from being created on any column.

The second part of the parameter setting needs to specify that a histogram is needed on the CUST_ID column. This is achieved using an additional part of the METHOD_OPT syntax that we haven’t mentioned yet, called FOR COLUMNS. The FOR COLUMNS syntax allows us to provide explicit instructions for specific columns listed in this part of the parameter setting. In this case we would use, FOR COLUMNS SIZE 254 CUST_ID to specify we need a histogram on the CUST_ID column. So the final METHOD_OPT parameter setting will be;

Below is the DBMS_STATS.GATHER_TABLE_STATS command in action. The only column that has a histogram created on it is the CUST_ID, even though several of the columns in the SALES table were used in the where clause of queries executed on the system.

BEGIN 
 dbms_stats.Gather_table_stats('SH', 'SALES', - 
 method_opt => 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID'); 
END; 
/

PL/SQL procedure successfully completed.

SELECT column_name, num_distinct, histogram 
FROM   user_tab_col_statistics 
WHERE  table_name = 'SALES'; 
 

There are several other cases where you may need to provide a more complex setting for the METHOD_OPT parameter. Take for example a scenario where you don’t want any form of statistics on a column. There is no way to tell Oracle don’t gather statistics on a particular column, so you need to do the reverse and explicitly list the column you want to have statistics gathered on using the FOR COLUMNS syntax. Let’s assume we don’t want statistics of any kind on the PROD_ID column of the SALES TABLE. Then the DBMS_STATS.GATHER_TABLE_STATS command would be as follows;

BEGIN 
 dbms_stats.delete_column_stats('SH', 'SALES', 'PROD_ID'); 
END; 
/

PL/SQL procedure completed successfully. 

BEGIN 
 dbms_stats.Gather_table_stats('SH', 'SALES',-
 method_opt => 'FOR COLUMNS SIZE 254 CUST_ID TIME_ID CHANNEL_ID PROMO_ID 
 QUANTITY_SOLD AMOUNT_SOLD'); 
END; 
/

PL/SQL procedure completed successfully.   

-- The average row length still got recorded accurately even though we did not gather statistics on the PROD_ID column.

SELECT num_rows, avg_row_len 
FROM   user_tables 
WHERE  table_name = 'SALES';

 

SELECT column_name, num_distinct, histogram 
FROM   user_tab_col_statistics 
WHERE  table_name = 'SALES';
 

Note the FOR COLUMNS syntax is only valid for the GATHER_TABLE_STATS procedure.
Finally, at the start of this post I mentioned that the METHOD_OPT parameter can also be used to create extended statistics. Extended statistics encompasses two additional types of column statistics; column groups and expression statistics. In the example below, a column group will be automatically created on the PROD_ID and CUST_ID column in the SALES table. It will be given a system-generated name and will have all of the base column statistics gathered on it.

BEGIN
dbms_stats.Gather_table_stats('SH', 'SALES',
method_opt => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 254(PROD_ID, CUST_ID)');
END;
/
 PL/SQL procedure successfully completed. 
 
SELECT column_name, num_distinct, histogram 
FROM   user_tab_col_statistics 
WHERE  table_name = 'SALES';

 

Rather than specifying the METHOD_OPT parameter in the statistics gathering command it is highly recommended that you specify any non-default value for the METHOD_OPT via DBMS_STATS.SET_TABLE_PREFS.

BEGIN
 dbms_stats.Set_table_prefs('SH', 'SALES', 'METHOD_OPT', -
 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 1 PROD_ID');
END;
/ 

In summary:
The METHOD_OPT parameter in the GATHER_DICTIONARY_STATS, GATHER_DATABASE_STATS, and GATHER_SCHEMA_STATS procedures only accepts 'FOR ALL [INDEXED|HIDDEN] columns' syntax. No specific column names can be specified.
When used in the GATHER_TABLE_STATS procedure, the METHOD_OPT parameter can accept an additional argument in the form of ‘FOR columns …'. Using this syntax allows you to controls;

• which columns to gather basic statistics
• which columns to gather histograms and the bucket size
• which extended statistics to create

Step by step document to create Standby Database

Standby database are very critical for disaster recovery. This article takes you step by step to setup Oracle Standby Database.
1 - Data Guard Operational Prerequisites

Same Oracle software release must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
The Primary Database must run in ARCHIVELOG mode.
The hardware and Operating system architecture on primary and standby location must be same.
Each primary and standby database must have its own control file.
If primary and standby databases are placed on the same system, initialization parameters must be adjusted correctly.
Primary database must be FORCE LOGGING mode.

2 - Preparing Primary Database for Standby Database creation

2.1 Ensure the primary database in ARCHIVELOG mode

Ensure the primary database in ARCHIVELOG mode using following command.

SQL> archive log list
Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           C:\oracle\ora92\database\archive\ORCLC
Oldest online log sequence    9
Next log sequence to archive  11
Current log sequence          11


2.2 Enable database in FORCE LOGGING mode

Place primary database in FORCE LOGGING mode using following SQL statement:

SQL> alter database force logging;
Database altered.

3 - Identify the primary database Datafiles

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF

10 rows selected.


4 - Make a copy of Primary Database

Make a closed backup copy of primary database by performing following steps:

4.1 Shutdown the Primary Database

Issue the following statement to shutdown the primary database.

SQL> shutdown immediate;


4.2 Copy the Datafiles to standby location

Copy the redo log files and Datafiles identified in section 3 to standby location.
Note: Primary Database must be shutdown while coping the files.

5 - Restart the Primary Database

Execute following command to restart the Primary Database.

SQL> startup;

6 - Create Control file for Standby Database

Issue the following command on primary database to create control file for the standby database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
  2  'D:\oracle\oradata\stby\control_sb01.ctl';

Database altered.

The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the last timestamp for the backup Datafiles.

7 - Create pfile from for standby database from the primary database
Create pfile from the server parameter file of the primary database; this pfile can be copied to standby location and modified.

SQL> CREATE PFILE='C:\oracle\ora92\database\initstby.ora' from spfile;

File created.

8 - Set initialization parameters on physical standby database

Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.

db_name - Not modified. The same name as the primary database.

compatible - Not modified. The same as the primary database, 9.2.0.0.0.

control_files - Specify the path name and filename for the standby control file.

log_archive_start - Not modified. The same as the setting for the primary database, TRUE

standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database.

db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.

log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.

log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)

standby_file_management - Set to AUTO.

remote_archive_enable - Set to TRUE.

instance_name - If this parameter is defined, specify a different value for the standby database than the primary database when the primary and standby databases reside on the same host.

lock_name_space - Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.

Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.

(Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service

If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window

C:\>oradim -new -sid stby -intpwd stby -startmode manual

10 - Configure listeners & tnsnames for standby and primary databases

Configure listeners in listeners.ora as follows

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = Orcl)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = Orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = stby)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = stby)
    )
  )

Restart the listeners using LSNRCTL utility.

% lsnrctl stop
% lsnrctl start

Also make an entry into tnsnames.ora for standby database.

stby =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraserver)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stby)
    )
  )

11 - Start Physical standby database

Start up the stand by database using following commands

C:\>set oracle_sid=stby

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

SQL> alter database mount standby database;
Database altered.

12 - Enabling archiving to Physical Standby Database

To configure archive logging from the primary database to the standby site the LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST_STATE_n parameters must be defined.

Issue following commands from primary database session:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

System altered.

13 - Initiate Log apply services

The example includes the DISCONNECT FROM SESSION option so that log apply services run in a background session.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> recover managed standby database cancel;
Media recovery complete.

Now go to primary database prompt

SQL> alter system switch logfile;

Go to stand by database prompt

SQL> alter database open read only;

Database altered.

14 - Verifying the Standby Database

On standby database query the V$ARCHIVED_LOG view to verify that redo log received.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME         NEXT_TIME
-------------- ------------------- ----------------------
         14 25-APR-05 16:50:34 25-APR-02 16:50:42
         15 25-APR-05 16:50:42 25-APR-02 16:50:47
         16 25-APR-05 16:50:47 25-APR-02 16:51:52

Archive the current log on the primary database using following statement.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

On standby database query the V$ARCHIVED_LOG view

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME         NEXT_TIME
-------------- ------------------- ----------------------
         14 25-APR-05 16:50:34 25-APR-02 16:50:42
         15 25-APR-05 16:50:42 25-APR-02 16:50:47
         16 25-APR-05 16:50:47 25-APR-02 16:51:52
         17 25-APR-05 16:51:52 25-APR-02 17:34:00

Now connect scott/tiger@orclc on primary database and create table or insert row in any table.

Now connect as sys on primary database and execute following SQL statement

SQL> alter system switch logfile;

On standby database execute following SQL statements

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database open read only;

Database altered.

And check whether the changes applied on the standby database or not.

15 - Annexure

15.1 Parameter file for Primary Database

*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\Orclc\bdump'
*.compatible='9.2.0.0.0'
*.control_files='C:\oracle\oradata\Orclc\CONTROL01.CTL',
'C:\oracle\oradata\Orclc\CONTROL02.CTL','C:\oracle\oradata\Orclc\CONTROL03.CTL'
*.core_dump_dest='D:\oracle\admin\Orclc\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='Orclc'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=C:\oracle\ora92\database\archive\ORCLC mANDATORY'
*.log_archive_dest_2='SERVICE=stby'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\Orclc\udump'

15.2 Parameter file for Standby Database

*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\stby\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\stby\CONTROL_SB01.CTL'
*.core_dump_dest='D:\oracle\admin\stby\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='Orclc'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=OrclcXDB)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='stby'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=D:\oracle\admin\stby\archive'
*.log_archive_dest_state_1=enable
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='NONE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\stby\udump'
*.standby_archive_dest='C:\oracle\ora92\database\archive\ORCLC'
*.db_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.log_file_name_convert='C:\oracle\oradata\Orclc','D:\oracle\oradata\stby'
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.lock_name_space=stby

ORACLE RAC ON/RAC OFF

Oracle's RAC: ON and OFF

In some cases, you may want to disable the RAC options for testing purposes -- perhaps to run a benchmark or convert the RAC binaries to single instance binaries. In such a case, you can use the following procedure to convert the RAC installation to non-RAC. Disabling and enabling RAC options are available only for UNIX platforms. Windows installations do not support relinking binaries with RAC ON and OFF.

Use the following steps to disable RAC (known as RAC OFF):

1. Log in as the Oracle software owner (which is typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib: cd $ORACLE_HOME/rdbms/lib
4. Run the following make command to relink the Oracle binaries without the RAC option: make -f ins_rdbms.mk rac_off

This normally runs for few minutes and should not pose any errors.
5. . Now relink the Oracle binaries: make -f ins_rdbms.mk ioracle

Now the Oracle binaries are relinked with the RAC OFF option. You may have to edit the init.ora or SPFILE parameters accordingly. If errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.

Use the following steps to enable RAC (known as RAC ON):

1. Log in as the Oracle software owner (typically the UNIX account oracle) in all nodes.
2. Shut down all the instances from all the nodes using a NORMAL or IMMEDIATE option.
3. Change the working directory to $ORACLE_HOME/rdbms/lib:
4. cd $ORACLE_HOME/rdbms/lib Run the following make command to relink the Oracle binaries without the RAC option:
make -f ins_rdbms.mk rac_on

This normally runs for a few minutes and should not pose any errors.
5. Now relink the Oracle binaries:
make -f ins_rdbms.mk ioracle

Now the Oracle binaries are relinked with the RAC ON option. You may need to edit the init.ora or SPFILE parameters accordingly. If any errors occur in step 4, you may need to contact Oracle Support and log a service request with the trace and log files.