Using srvctl to Manage your 10g RAC Database

Oracle recommends that RAC databases be managed with srvctl, an Oracle-supplied tool that was first introduced with 9i RAC. The 10g version of srvctl is slightly different from the 9i implementation. In this article.

How and why -- to manage your 10g databases with srvctl.

RAC Architecture Overview

Brief overview of RAC architecture.

1. A cluster is a set of 2 or more nodes that share or coordinate resources to perform the same task.

2. A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.

3. Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.

Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.

Cluster Ready Services and the OCR

Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all
platforms.

CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks: the Oracle Cluster Registry (OCR), and the voting disk.

CRS manages the following resources:

1. The ASM instances on each node
2. Databases
3. The instances on each node
4. Oracle Services on each node
5. The cluster nodes themselves, including the following processes:

1. VIP
2. GSD
3. The listener
4. The ONS daemon

CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.

Interacting with CRS and the OCR: srvctl

srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.

Using srvctl

Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.

srvctl must be run from the $ORACLE_HOME/bin of the RAC you are administering. The basic format of a srvctl command is

$ ./srvctl
Usage: srvctl
[]
command:
enabledisablestartstoprelocatestatusaddremovemodifygetenvsetenvunsetenvconfig
objects: databaseinstanceservicenodeappsasmlistener
For detailed help on each command and object and its options use:
srvctl -h
Examples of starting and stopping RAC follow:-

$ gsdctl stat
GSD is running on local node

$ gsdctl stop

$ gsdctl start

Start all its instances and all its services, on all nodes.
$ srvctl start database -d test

Stop all its instances and all its services, on all nodes.
$ srvctl stop database -d test

Startup up the TEST1 instance of the TEST database
$ srvctl start instance -d test -i test1

Bounce the TEST1 instance of the TEST database
$ srvctl stop instance -d test -i test2


To check the CRS status.


# ./crs_stat -t


Name Type Target State Host
------------------------------------------------------------
ora....D1.inst application OFFLINE OFFLINE
ora....D2.inst application OFFLINE OFFLINE
ora.test.db application OFFLINE OFFLINE
ora....od1.gsd application ONLINE ONLINE test1
ora....od1.ons application ONLINE ONLINE test1
ora....od1.vip application ONLINE ONLINE test2
ora....od2.gsd application ONLINE ONLINE test2
ora....od2.ons application ONLINE ONLINE test2
ora....od2.vip application ONLINE ONLINE test1


After db upgrade, Here some crs services are went OFFLINE.


Solution:


Please remove the DBSID from srvctl and add again, just follow the bellow steps.


su - oracle

$ cd $ORACLE_HOME/bin


$ ./srvctl remove database -d test

Remove the database test? (y/[n]) y


$ ./srvctl add database -d test -o /10.2.0/test/app/oracle
$ ./srvctl add instance -d test -i test1 -n test1
$ ./srvctl add instance -d test -i test2 -n test2
$ ./srvctl start database -d test


# ./crs_stat -t

Name Type Target State Host
------------------------------------------------------------
ora....D1.inst application ONLINE ONLINE test1
ora....D2.inst application ONLINE ONLINE test2
ora.test.db application ONLINE ONLINE test1
ora....od1.gsd application ONLINE ONLINE test1
ora....od1.ons application ONLINE ONLINE test1
ora....od1.vip application ONLINE ONLINE test1
ora....od2.gsd application ONLINE ONLINE test2
ora....od2.ons application ONLINE ONLINE test2
ora....od2.vip application ONLINE ONLINE test2



http://www.orafaq.com/node/70

http://www.oracledba.org/11g/rac/11g_RAC_Admin_Utilities.html

Cluster Commands on Sun Solaris

vxvm
vxprint -ht
su -8 nodes
vxvm -32 nodes
fail over application acive-passivve
load balancing active-activequorom votes =n/2+1=no of nodes
path=/etc/cluster/ccr/infrasturcture
ammesia(starting last in first)
#boot -x non cluster node
#ccradm
hearpeat means keep alive packets
clster version 3.1,3.2,3.0 nafo (network adapter fail over)
ipmp means ip multipathing
did means disk id it is called global devices
command =/usr/cluster/bin
#scdidadm -L (dis device available)
#scdisadm -l (local device)
scdidadm -r (new device add in cluseteR)
#mount -g will mount in an nodes /global min 512mb on each node
path
manpath
#scsetup (menu drivers)
#scconf -p (to see the configuration)
#scstat -q (status of the cluser)
#scconf -p (view ot configuration)
#scstat -W -h primary nodename
#/.rhost
path install cluster=suncluster_3.0/tools/scinstall
..
#scstat
#scconf -p
scdidadm -L
#scconf - transp-jct
#scconf -transp-jct-etherhup
checking node id /etc/cluster/nodeid varlog=/var/cluster/logs/install/scinstall.bg.1374
#scstat -n
reboot --r
#scshutdown -y -g0
#scconf -a -q globaldev=d3
#scconf -a -q reset
#metaset -s disksetname -a -m hostname
#metstat -s diskname
#scstat -q (only quorum)
#scswitch -F -D simvol (online diskgroup)
#scswitch -Z -D simvol -h e3500
#scswitch -S -H E4500
#scswitch -m -D offline DG
#scswitch -r -D restart DG
#scrgadm -a -g share -h e4500,e3400
#scswitch -F -g (resourse GN)
#scswitch -Z -g -h hostname
#scswitch -n -j
#scswitch -e -j
scrgadm -r -j
#scswitch -v -g
#scswitch -o -g
scrgadm -r -g
boot -m milestone=none

RAC Installation through RAPID Clone

RAC installation:

1. Initially, the Sun team would have finished he clustering between two nodes. This is to ensure that the number of nodes are correct in the installation. You can always add extra nodes to a server. A cluster needs to be setup between the n number of nodes, we will be using. By nodes, here I mean only database nodes.

2. Stage the Oracle Database installation products. It is just a copy command of all the files present here.

3. Installation of Oracle Home:
Here we are installing only the home and not the database. It's because, the basic setup of RAC illustrates a shared storage device which would house all the database files, redo log files, control files and the SP file. The listener specific files and the archivelog files would be present in the specific servers as they are instance specific.

4. In the installation of the home, select "Customised" in the Database configuration.

5. There occurs an entry for the Shared Configuration file.
Here we need to create a file of the name "para_raw_device_file" which would contain the details of all the volume manager of all the dbf files, redo log files, control files and the spfile. Entries would look like this.

rbs03=/dev/vx/rdsk/racdg/rbs03
temp10=/dev/vx/rdsk/racdg/temp10
cntrl01=/dev/vx/rdsk/racdg/cntrl01
cntrl02=/dev/vx/rdsk/racdg/cntrl02
cntrl03=/dev/vx/rdsk/racdg/cntrl03
spfile=/dev/vx/rdsk/racdg/spfile

6. /dev/vx/rdsk/racdg would be the path in the RAW device. This is set up by the hardware team, who would be configuring the RAW device. Here the main idea is to ensure a faster operation of the servers as no file system is mounted on the RAW device. And importantly, before we copy the files to the RAW device, we should have a count of the database files, redo log files and the control files and create them in the RAW device using Veritas Volume Manager. The size of the data files need to pre-determined and then they should be created prior to moving the datafiles to RAW. Another important aspect is that, any file which is going to be present in the RAW device will not have any file extension.

7. So system01.dbf would be system01 and cntrl01.ctl would be cntrl01

8. Now the OUI takes you to listener configuration and the port pool selection. We finish thses processes here and proceed on towards the Manual database creation using dbca.

rdsk contains files of the format
crw------- 1 oracle dba 295, 91019 Oct 10 21:29 system01
crw------- 1 oracle dba 295, 91021 Oct 10 21:29 system02
crw------- 1 oracle dba 295, 91022 Oct 10 21:29 system03

and /dev/vx/dsk contains files of the format
brw------- 1 root root 295, 15000 Sep 8 19:05 archlog

Sun cluster is used to manage clusters between two nodes, while Oracle Cluster is used to manage clusters between two Oracle Instances.

GSD configuration:
Global Services Daemon

go to Oracle home/ bin

$ gsdctl start

Go to the ORacle home/bin and give

$./dbca –datafileDestination /dev/vx/rdsk/racdg/

Now we need to select the Oracle CLuster database.

Dedicated or Shared Server (MTS) - ????


RAC Steps

Preparing for the installation of RAC

Configure the shared disks

Create the directory /var/opt/oracle and set ownership to the oracle user.
If this installation creates the first ORACLE_HOME on your cluster for Real Application Clusters, then set the SRVM_SHARED_CONFIG environment variable to the name of the 100MB volume raw device or shared file that you have pre-created and set up for configuration storage by entering, for example:

setenv SRVM_SHARED_CONFIG shared_filename

Where shared_filename is the name of the configuration raw device file. If you are using a cluster file system, then make sure the partition on which this file resides has least 100MB of free space.

Set the DBCA_RAW_CONFIG environment variable. Do this, for example, by executing the following where filename is the name of the ASCII file containing the configuration information:

setenv DBCA_RAW_CONFIG filename

If this is the first installation on your cluster and you have not set the environment variable, then the Installer prompts you for the name of the 100MB file by displaying the Shared Configuration File Name page

$cd /var/opt/oracle
$ cat srvConfig.loc
srvconfig_loc=/dev/vx/rdsk/racdg/rac_srvconfig_100m

$ SRVM_SHARED_CONFIG=/dev/vx/rdsk/racdg/rac_srvconfig_100m
$ export SRVM_SHARED_CONFIG

$DBCA_RAW_CONFIG=para_raw_device_file
$export DBCA_RAW_CONFIG

$ DISPLAY=172.16.1.72:0.0
$ export DISPLAY
srvconfig_loc=/var/opt/oracle/srvConfig.loc

root@cmwdb1 # ./orainstRoot.sh
Creating Oracle Inventory pointer file (/var/opt/oracle/oraInst.loc)
Changing groupname of /oracle/oraInventory to dba.


Establish system environment variables

Ø Set a local bin directory in the user's PATH, such as /usr/local/bin, or /opt/bin. It is necessary to have execute permissions on this directory.
Ø Set the DISPLAY variable to point to the system's (from where you will run OUI) IP address, or name, X server, and screen.
Ø Set a temporary directory path for TMPDIR with at least 20 Mb of free space to which the OUI has write permission.

$ set
CLASSPATH=/oracle/9.2.3/JRE:/oracle/9.2.3/jlib:/oracle/9.2.3/rdbms/jlib:/oracle/
9.2.3/network/jlib
DBCA_RAW_CONFIG=para_raw_device_file
DISPLAY=172.16.1.72:0.0
HOME=/oracle/9.2.3
HZ=
IFS=
LANG=C
LOGNAME=oracle
MAIL=/var/mail/oracle
MAILCHECK=600
NLS_DATE_FORMAT=DD-MON-RR
NLS_LANG=AMERICAN_AMERICA.US7ASCII
NLS_NUMERIC_CHARACTER=.,
NLS_SORT=binary
OPTIND=1
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/9.2.3
ORACLE_TERM=xterm
ORA_NLS33=/oracle/9.2.3/ocommon/nls/admin/data
PATH=/oracle/9.2.3/bin:/usr:/usr/bin:/usr/lib:/etc:/usr/ucb
PS1=$
PS2=>
SHELL=/bin/sh
SRVM_SHARED_CONFIG=/dev/vx/rdsk/racdg/rac_srvconfig_100m
TERM=dtterm
TZ=Asia/Calcutta


Set the /etc/system files.
Kernel Parameter Setting Purpose

SHMMAX 4294967295 Maximum allowable size of one shared memory segment (4 Gb)
SHMMIN 1 Minimum allowable size of a single shared memory segment.
SHMMNI 100 Maximum number of shared memory segments in the entire system.
SHMSEG 10 Maximum number of shared memory segments one process can attach.
SEMMNI 1024 Maximum number of semaphore sets in the entire system.
SEMMSL 500 Minimum recommended value. SEMMSL should be 10 plus the largest PROCESSES parameter of any Oracle database on the system.
SEMMNS 1024 Maximum semaphores on the system. This setting is a minimum recommended value. SEMMNS should be set to the sum of the PROCESSES parameter for each Oracle database, add the largest one twice, plus add an additional 10 for each database.
SEMOPM 100 Maximum number of operations per semop call.
SEMVMX 32767 Maximum value of a semaphore.
(swap space) xx MB Two to four times your system's physical memory size.

set md:mirrored_root_flag=1
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=200
set shmsys:shminfo_shmmni=200
set shmsys:shminfo_shmseg=200
set semsys:seminfo_semmsl=2048
set semsys:seminfo_semmns=2048
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
set semsys:seminfo_semmni=2048
set semsys:seminfo_semmap=1024
set semsys:seminfo_semmnu=2048
set semsys:seminfo_semume=200

root@cmwdb1 # cat /oracle/9.2.3/para_raw_device_file
system=/dev/vx/rdsk/racdg/system
spfile=/dev/vx/rdsk/racdg/spfile
users=/dev/vx/rdsk/racdg/users
temp=/dev/vx/rdsk/racdg/temp
undotbs1=/dev/vx/rdsk/racdg/undotbs1
undotbs2=/dev/vx/rdsk/racdg/undotbs2
example=/dev/vx/rdsk/racdg/example
cwmlite=/dev/vx/rdsk/racdg/cwmlite
indx=/dev/vx/rdsk/racdg/indx
tools=/dev/vx/rdsk/racdg/tools
drsys=/dev/vx/rdsk/racdg/drsys
control01=/dev/vx/rdsk/racdg/controlfile1
control02=/dev/vx/rdsk/racdg/controlfile2
redo1_1=/dev/vx/rdsk/racdg/log11
redo1_2=/dev/vx/rdsk/racdg/log12
redo2_1=/dev/vx/rdsk/racdg/log21
redo2_2=/dev/vx/rdsk/racdg/log22


Configuring the existing system to RAC... Follow 279956.1 Metalink note.

Pre Clone existing database and Apps server.

DBTier


1. perl adpreclone.pl dbTier

AppsTier


2. perl adpreclone.pl appsTier


Then Copy datafile and Apps file to target node.

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

Post clone Target database

dbTier

1. perl adcfgclone.pl dbTier

appsTier
2. perl adcfgclone.pl appsTier


Take backup control file to trace


sql> alter database backup controlfile to trace


Then copy the datafile to RAW device.

1. Before coping you need to create volume manager on RAW Device.

eg

dd if=/cmwdb1/cmwproddata/system01.dbf of=/dev/vx/rdsk/racdg/system01 bs=8192

2. after coping the datafile, Recreate the controlfile on oracle new home.

new home(9.2.3)
old home(9.2.0.3)

eg,

CREATE CONTROLFILE REUSE SET DATABASE CMWPROD RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 2722
LOGFILE
GROUP 1 (
'/dev/vx/rdsk/racdg/log01a',
'/dev/vx/rdsk/racdg/log01b'
) SIZE 20M,
GROUP 2 (
'/dev/vx/rdsk/racdg/log02a',
'/dev/vx/rdsk/racdg/log02b'
) SIZE 20M,
GROUP 3 (
'/dev/vx/rdsk/racdg/log03a',
'/dev/vx/rdsk/racdg/log03b'
) SIZE 20M
-- STANDBY LOGFILE
DATAFILE
'/dev/vx/rdsk/racdg/system01',
'/dev/vx/rdsk/racdg/system02',
'/dev/vx/rdsk/racdg/system03',
'/dev/vx/rdsk/racdg/system04',
'/dev/vx/rdsk/racdg/system05',
'/dev/vx/rdsk/racdg/ctxd01',
'/dev/vx/rdsk/racdg/owad01',
'/dev/vx/rdsk/racdg/applsysd01',
'/dev/vx/rdsk/racdg/applsysx01',

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

database startup

Then,

Copy the appsutil, oui21, appsoui to new home.

verify metalink 165195.1


1. Then run adbldxml.sh /oraclehome/appsutil/bin
2. run adconfig.sh
3. Goto apps tier and run admkappsutil.pl scripts
4. copy appstil.zip to Oracle home
5. unzip appsutil.zip
6. perl adpreclone.pl database
7. perl adcfgclone.pl database
Database one instance created.

You run the same steps to another node:

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

zip the appsutil folder in new oracle home 1 and copy to the second node

> perl adcfgclone.pl database
40% error occur

you need to configure thread2

ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 4 ( '/dev/vx/rdsk/racdg/log4a', '/dev/vx/rdsk/racdg/log4b' ) SIZE 20M,
GROUP 5 ( '/dev/vx/rdsk/racdg/log5a', '/dev/vx/rdsk/racdg/log5b' ) SIZE 20M,
GROUP 6 ( '/dev/vx/rdsk/racdg/log6a', '/dev/vx/rdsk/racdg/log6b' ) SIZE 20M;
ALTER DATABASE ENABLE PUBLIC THREAD 2;

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

Again you run

$perl adcfgclone.pl database

=========================================
Now you need configure listener file and tnsnames file.
=========================================

Data guard

Data guard

Introduction:
Dataguard is configured to ensure the availability of database in case of disaster.
This document explains how to configure the physical standby database for switchover and fail over operations.

Prerequest:

• 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.

Preparing Primary Database for standby creation
To ensure that database in archive log mode, execute the following command.

SQL>archive log list

Enable database in Force logging mode

SQL>Alter database force logging;

Identify the location of the datafiles
SQL>select name from v$datafile;

Make a copy of the primary database

First step: Shutdown the primary database

SQL>shutdown immediate;

Second step: copy the datafiles manually to standby location

SQL>startup;

Create the standby control file by using the following command

SQL>alter database create standby controlfile as ‘D:\oracle\oradata\controlstby.ctl’

Create pfile from spfile. And save the same pfile to standby location;

Set the initialization parameters for the primary database and standby database as given below
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 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'


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

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)
)
)


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.