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.

RMAN QUERY TO FIND BACKUP DETAILS

 RMAN  VIEWS (WHEN NO RECOVERY CATALOG AVAILABLE) . i have provide the example as per 11gR2. 

To Display the backup details of the database (Full, Incremental & Archive log backup), when the DB is not connect with the recover catalog.

SQL> select session_key,

       input_type,

       status,

       to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,

       to_char(end_time,'yyyy-mm-dd hh24:mi')   end_time,

       output_bytes_display,

       time_taken_display

from v$rman_backup_job_details

order by session_key asc;

Below query will display the backup piece that hold the backup of archive log, datafile in the DISK or TAPE Drive.

Query to see Backed Up Archive Logs in the database

SQL> select recid,set_stamp,sequence#,first_change#,next_change#

                 from  v$backup_redolog;

Query against the V$BACKUP_PIECE view to find backup details of a particular archive log 

  SQL>            select r.sequence#, p.handle from v$backup_piece p, v$backup_redolog r

            where r.set_stamp = p.set_stamp and r.set_count = p.set_count

              and r.sequence# = 63

Note: 63 is the sequence number.

Query against the V$BACKUP_PIECE view to find backup details of a particular datafiles

SQL> select d.file#, p.handle    from v$backup_piece p, v$backup_datafile d  where d.set_stamp = p.set_stamp  and d.set_count = p.set_count and d.file# = 3

Note: 3 is the datafile number.

SQL>  select table_name from dict where table_name like 'V$%RMAN%';

TABLE_NAME

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

V$RMAN_BACKUP_JOB_DETAILS

V$RMAN_BACKUP_SUBJOB_DETAILS

V$RMAN_BACKUP_TYPE

V$RMAN_COMPRESSION_ALGORITHM

V$RMAN_CONFIGURATION

V$RMAN_ENCRYPTION_ALGORITHMS

V$RMAN_OUTPUT

V$RMAN_STATUS

8 rows selected.

V$RMAN_BACKUP_JOB_DETAILS è displays details about backup jobs.

V$RMAN_BACKUP_SUBJOB_DETAILS è  merges similar operations within an RMAN session into a single row. For example, if there are four BACKUP DATAFILE <n> commands, three RECOVERY COPY OF DATAFILE commands, and one BACKUP RECOVERY AREA command, this view will contain three rows - one each for BACKUP, ROLLFORWARD, and COPY_DISK_TO_TAPE operation.

V$RMAN_BACKUP_TYPEè displays information about RMAN backup types.

SQL> select * from V$RMAN_BACKUP_TYPE;

    WEIGHT INPUT_TYPE

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

         1 BACKUPSET

         2 SPFILE

         3 CONTROLFILE

         4 ARCHIVELOG

         5 DATAFILE INCR

         6 DATAFILE FULL

         7 DB INCR

         8 RECVR AREA

         9 DB FULL

V$RMAN_COMPRESSION_ALGORITHM è provides descriptions of supported compression algorithms. It is used by the RMAN client.

V$RMAN_CONFIGURATION è Information about RMAN persistent configuration settings.

V$RMAN_ENCRYPTION_ALGORITHMS è displays supported encryption algorithms. It is used by the RMAN client to validate user-requested algorithms. This view will list AES128, AES192, and AES256 encryption algorithms for the current release. The default algorithm is AES128.

V$RMAN_OUTPUT è  displays messages reported by RMAN. This is an in-memory view and is not recorded in the controlfile. The view can hold 32768 rows.

V$RMAN_STATUS è displays the finished and on-going RMAN jobs. For on-going jobs, this view displays progress and status. The jobs which are in progress are stored only in memory while the finished jobs are stored in the controlfile

10g Transportable tablespaces

PURPOSE
-------
Use transportable tablespace in 10G database to move tablespaces between different Operating Systems.


SCOPE & APPLICATION
-------------------
With previous releases of the Oracle Database, you could not transport a tablespace to a target database if a tablespace with the same name already existed in the target database. 
With Oracle Database 10G, you can simply rename the tablespace in the target database to some other name before the transport.

Special Considerations:
Before issuing this command, make sure the compatibility level is set to at least 10.0.0.
If any datafile in the tablespace is offline or if the entire tablespace is offline, then the tablespace cannot be renamed.
Renaming a tablespace does not change its tablespace identifier. If the tablespace is READ ONLY, then this command does
not update datafile headers. You  get a message in the alert log indicating the datafile headers are not updated.  These
headers can only be changed if the tablespace is put back in READ WRITE mode, if ever possible.  If the tablespace is
an undo tablespace, and it is the tablespace specified by the initialization parameter UNDO_TABLESPACE in any
instances, then they are changed to reflect the new name. These changes affect both memory and SPFILE.  All
these changes are logged in the alert log file.  If SPFILE is not being used, then a specific message is added
to the alert log file advising DBAs to manually change the corresponding initialization parameter file.

Note: Users with ALTER TABLESPACE privilege are able to use this feature.  It is not possible to rename the SYSTEM or SYSAUX tablespaces.


TRANSPORTABLE TABLESPACE BETWEEN DIFFERENT Operating Systems
-------------------------------------------------------------

1. Set the tablespace to READ ONLY
   -------------------------------
SQL> alter tablespace TRANSPORTABLE_TESTE read only;

Tablespace altered.

2. Export metadata
   ---------------
      WINDOWS - exp userid=sys as sysdba transport_tablespace=y tablespaces=TRANSPORTABLE_TESTE file=ts.dmp log=exp_ts.log statistics=none

Export: Release 10.1.0.2.0 - Production on Qui Out 28 00:16:11 2004

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Conectado a: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options
Exportação executada no conjunto de caracteres de WE8MSWIN1252  e no conjunto de caracteres de AL16UTF16 NCHAR
OBS: dados (linhas) da tabela não serão exportados
Prestes a exportar metadados de tablespace transportavel...
Para tablespace TRANSPORTABLE_TESTE ...
. exportando definições de cluster
. exportando definições de tabela
. exportando restrições referenciais de integridade
. exportando gatilhos
. exportação final de metadados de tablespace transportavel
Exportação encerrada com sucesso, sem advertências.


3. Check the endianness of the target database
  
For check platform do the following:

SQL> select * from v$transportable_platform;

PLATFORM_ID   PLATFORM_NAME                                              ENDIAN_FORMAT
-----------   --------------------------------------------------------   --------------
          1   Solaris[tm] OE (32-bit)                                    Big
          2   Solaris[tm] OE (64-bit)                                    Big
          7   Microsoft Windows IA (32-bit)                              Little
         10   Linux IA (32-bit)                                          Little
          6   AIX-Based Systems (64-bit)                                 Big
          3   HP-UX (64-bit)                                             Big
          5   HP Tru64 UNIX                                              Little
          4   HP-UX IA (64-bit)                                          Big
         11   Linux IA (64-bit)                                          Little
         15   HP Open VMS                                                Little
          8   Microsoft Windows IA (64-bit)                              Little
          9   IBM zSeries Based Linux                                    Big
         13   Linux 64-bit for AMD                                       Little
         16   Apple Mac OS                                               Big
         12   Microsoft Windows 64-bit for AMD                           Little

15 linhas selecionadas.

      The source platform is Microsoft Windows IA (32-bit): endianness Little
      The target platform is HP-UX (64-bit)               : endianness Big

    You have to convert the files :
    --> locally before the import step so that the files are endian compatible:

    <Windows> rman target=/

Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.
conectado ao banco de dados de destino: ORCL (DBID=1068469100)

RMAN> convert tablespace 'TRANSPORTABLE_TESTE' to platform="HP-UX (64-bit)" db_file_name_convert='D:\ORACLE\SERVER10G\ORCL\TRANSPORTABLE_TESTE.ORA', 'D:\TRANSPORTABLE_TESTE01.dbf';

Iniciando backup em 28/10/04
usar o arquivo de controle do banco de dados de destino em vez do catalogo de recuperação
canal alocado: ORA_DISK_1
canal ORA_DISK_1: sid=148 devtype=DISK
canal ORA_DISK_1: iniciando conversao de arquivo de dados
fno=00006 name=D:\ORACLE\SERVER10G\ORCL\TRANSPORTABLE_TESTE.ORA do arquivo de dados de entrada arquivo de dados convertido=D:\TRANSPORTABLE_TESTE01.DBF
canal ORA_DISK_1: conversao de arquivo de dados concluida, tempo decorrido: 00:00:03
Finalizado backup em 28/10/04

The converted datafile is staged in /tmp directory until it is copied to the target server.
--> remotely on the target server after having copied them on the server.


4. Move datafiles and export dump file
   -----------------------------------
      C:\> ftp ts.dmp 
           D:\TRANSPORTABLE_TESTE01.dbf


5. Import metadata
   ---------------
      $ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y
                                   datafiles=/TMP/TRANSPORTABLE_TESTE01.dbf
                                   file=ts.dmp log=imp_ts.log
  
     
6. Set the tablespace to READ WRITE
   --------------------------------
      SQL> alter tablespace TRANSPORTABLE_TESTE read write;
      Tablespace altered.