Background : I have installed OMS/OMR and OMA successfully. In theory I should see OMA client successfully configured at OMS grid console but

$ ./emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.3.0
OMS Version       : 10.2.0.2.0
Protocol Version  : 10.2.0.2.0
Agent Home        : /opt/oracle/product/oracle10gAgent/agent10g
Agent binaries    : /opt/oracle/product/oracle10gAgent/agent10g
Agent Process ID  : 23314
Parent Process ID : 23295
—————————————————————
Agent is Running but Not Ready

To locate an exact error look at OMS files $OMS_HOME\sysman\log\emoms.log and $OMS_HOME\sysman\log\emoms.trc

My  $OMS_HOME\sysman\log\emoms.trc show error at  Trigger ‘SYSMAN.EM_VIOLATION_CHECKS’

2008-12-02 15:00:37,180 [SeverityLoad https://bmhp01:3872/emd/main/] ERROR XMLLoader.Handler processInsertException.462 – java.sql.SQLException: ORA-20612: Internal error processing severity. Closing availability record with negative duration for :  (target_guid = 1C2A2131D1A40C621929D7CE63032001) (collection_ts = 2008-12-02 14:47:07) (current_status = AGENT DOWN)
ORA-06512: at “SYSMAN.EM_SEVERITY”, line 2110
ORA-06512: at “SYSMAN.EM_SEVERITY”, line 3287
ORA-06512: at “SYSMAN.EM_VIOLATION_CHECKS”, line 791
ORA-04088: error during execution of trigger ‘SYSMAN.EM_VIOLATION_CHECKS’

Check at Google or metalink for further details on it.

– Sometimes you may have to delete records from OMR tables to fix this issue see Note:603011.1

– For ORA-04088: during execution of trigger SYSMAN.TARGETS_INSERT_TRIGGER , locate TARGET_GUID/AGENT_GUID at emoms.log  for example CE6B5C2E232766E01D3803EB03D70128 and run “delete from MGMT_TARGET_ASSOC_INSTANCE where target_guid = HEXTORAW(‘TARGET_GUID’);”

How to fix it? For clustered install (RAC/OS cluster) please see details here

$ ./emctl stop agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Stopping agent … stopped.

Delete all files in $AGENT_HOME/sysman/emd/upload and $AGENT_HOME/sysman/emd/state

$ ./emctl clearstate agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
EMD clearstate completed successfully

$ ./emctl unsecure agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Checking Agent for HTTP…   Done.
Agent is already stopped…   Done.
Unsecuring agent…   Started.
OMS Upload URL – http://ASST105157:4889/em/upload/ is locked or unavailable.

$ ./emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
EMD upload completed successfully

$ ./emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.3.0
OMS Version       : 10.2.0.2.0
Protocol Version  : 10.2.0.2.0
Agent Home        : /opt/oracle/product/oracle10gAgent/agent10g
Agent binaries    : /opt/oracle/product/oracle10gAgent/agent10g
Agent Process ID  : 23314
Parent Process ID : 23295
Agent URL         : https://bmhp01:3872/emd/main/
Repository URL    : https://ASST105157:1159/em/upload
Started at        : 2008-12-02 14:52:07
Started by user   : oracle
Last Reload       : 2008-12-02 14:52:07
Last successful upload                       : 2008-12-02 15:05:03
Total Megabytes of XML files uploaded so far :     5.25
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    32.43%
Last successful heartbeat to OMS             : 2008-12-02 15:04:50
—————————————————————
Agent is Running and Ready

$ ./emctl secure agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
Enter Agent Registration password :
Agent successfully stopped…   Done.
Securing agent…   Started.
Requesting an HTTPS Upload URL from the OMS…   Done.
Requesting an Oracle Wallet and Agent Key from the OMS…   Done.
Check if HTTPS Upload URL is accessible from the agent…   Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode…   Done.
EMD_URL set in /opt/oracle/product/oracle10gAgent/agent10g/sysman/config/emd.properties
Securing agent…   Successful.

I can see new new target at grid console

Download OMS software from here

I am using Windows 10.2.x release for my OMS install. Due to limited hardware I am installing OMS (oracle management service or GRID console) on windows box while OMR (OMS database) configured at remote HP True 64 system. I have a separate database instance called “oms” for OMR.

Following diagram will give you an idea on OMS/OMR and OMA

Towards end of install, Oracle will configure default configuration and will take some time depending on underlying hardware. Please be patient until you see status=”SUCCEEDED” at GUI Installer.

Let go ahead and install Grid Agent at clients for monitoring.

How to start a GRID Console?

1.  OMR : Please start OMR (Oracle Management Repository)database as well a listener

2.  OMA : If you are using windows make sure “XXX10gAgent” service is started, for Unix/Linux use “emctl start agent” to start an agent

3. OMS : OMS could be controlled using number of methods

a. Use opmnctl to start and stop Oracle OMS

C:\oracle\product\10.2.0\oms10g\opmn\bin>opmnctl stopall
opmnctl: stopping opmn and all managed processes...

At this point http://asst105157:4889/em/console/ will return an error use following command to start all OMS components

C:\oracle\product\10.2.0\oms10g\opmn\bin>opmnctl startall
opmnctl: starting opmn and all managed processes...

C:\oracle\product\10.2.0\oms10g\opmn\bin>opmnctl status -l
Processes in Instance: EnterpriseManager0.asst105157

-ias-component     | process-type       |     pid | status   |        uid |  memused |    uptime | ports-------------------+--------------------+---------+----------+------------+----------+-----------+------DSA                | DSA                |     N/A | Down     |        N/A |      N/A |       N/A | N/AHTTP_Server        | HTTP_Server        |    4460 | Alive    |  607649821 |    43796 |  00:03:11 | http1:7778,http2:7200,https1:4444,https2:1159,http3:4889
LogLoader          | logloaderd         |     N/A | Down     |        N/A |      N/A |       N/A | N/Adcm-daemon         | dcm-daemon         |     N/A | Down     |        N/A |      N/A |       N/A | N/AOC4J               | home               |    5620 | Alive    |  607649822 |    30928 |  00:03:34 | ajp:12501,rmi:12401,jms:12601
OC4J               | OC4J_EM            |    5756 | Alive    |  607649823 |    30564 |  00:03:34 | ajp:12502,rmi:12402,jms:12602
OC4J               | OC4J_EMPROV        |    1260 | Alive    |  607649824 |    30796 |  00:03:34 | ajp:12503,rmi:12403,jms:12603
WebCache           | WebCache           |    4684 | Alive    |  607649825 |    31372 |  00:03:34 | http:80,invalidation:9401,statistics:9402
WebCache           | WebCacheAdmin      |     988 | Alive    |  607649826 |     9088 |  00:03:34 | administration:9400

b. Use emctl to start, stop, or check the status of Application Server Control

Start  iasconsole using “emctl start iasconsole”  and try http://<Host IP>:1810 to manage OMS components

c. Use dcmctl   to start and stop Oracle OMS

C:\oracle\product\10.2.0\oms10g\dcm\bin>dcmctl stop
Current State for Instance:EnterpriseManager0.asst105157
    Component               Type          Up Status     In Sync Status
================================================================================
1   home                    OC4J          Down          True
2   HTTP_Server             HTTP_Server   Down          True
3   OC4J_EM                 OC4J          Down          True
4   OC4J_EMPROV             OC4J          Down          True

At this point http://asst105157:4889/em/console/ will return an error use following command to start all OMS components

C:\oracle\product\10.2.0\oms10g\dcm\bin>dcmctl start
Current State for Instance:EnterpriseManager0.asst105157
    Component               Type          Up Status     In Sync Status
================================================================================
1   home                    OC4J          Up            True
2   HTTP_Server             HTTP_Server   Up            True
3   OC4J_EM                 OC4J          Up            True
4   OC4J_EMPROV             OC4J          Up            True

For listing OMS components and Status use “dcmctl getstate” else “dcmctl listcomponents”

See

1. Download Software and upload zip at server. You will need 10.2.0.1 else 10.2.0.2 base release before you upgrade to 10.2.03/10.2.0.4
2. You will also need an X windows environment like cygwin installed on your desktop for remote install.  You can also carry a silent install with no X windows interface.
3. Let’s setup cygwin on client window machine

Goto c:\cygwin folder and fire Cygwin.bat . At $ prompt enter “startx”

You will receive a new window like below. Now enter “xhost + <hostname/IP address>” to allow server connection.

I entered “xhost +” to allow connection from any machine

Start Command Prompt on your desktop and locate IP address using “ipconfig”

On Server export DISPLAY=<IP Address>:0.0  and start “runInstaller”

You will see Oracle Installer window like below. If there are errors returned at telnet console , fix them before you proceed.

If your OS is running a cluster file system then Oracle will prompt for a clustered node install

Wait until “Agent Configuration Assistant” return a SUCCESS

Run “emctl status agent” and make sure last line says “Agent is Running and Ready”

Login at grid control and click on “hosts” tab. Here I could locate my new host

Click on Targets to see list of databases at new Host

Another click at “Databases” should show number of active Oracle Instances at Node

See number displayed against “Targets Not Configured” . You will need to configure DBSNMP oracle user for Grid monitoring.

Problem Statement I am configuring Oracle Agent for a grid console. My system is HP TRUE64 and /opt/oracle/Agent10g is a clustered file system like QFS/GFS/OCFS. A clustered file system is shared between 2 nodes, in my case $ORACLE_HOME is shared between cluster1 and cluster2.

I could get number of standalone agents working except this one because it is on a clustered file system.

Agents are on a clustered file system so thinking each one of them need separate config files , I made 2 separate copies of most configuration files
– emd.properties
– /opt/oracle/product/agent10g/sysman/config/b64LocalCertificate.txt
-/opt/oracle/product/agent10g/sysman/config/b64InternetCertificate.txt
Also set a different directory on each server for
– emdWalletDest=/opt/oracle/product/agent10g/sysman/config/server
I could connect to Agent URL : http://Agent:3872/emd/main/ & Repository URL : http://oms:4889/em/upload/ successfully.

Result : When I start Agent on one machine , the agent running on other machine shuts down. I almost decided to reinstall individual agent but then came across oracle documentation  “Reconfiguring a Standalone Agent to an Oracle RAC Agent”
Reconfiguration of a standalone agent occurs when you want to configure this agent (with standalone configurations) as a Oracle RAC agent.
To reconfigure a standalone agent as a Oracle RAC agent, you must execute the agentca script with the following options:
<Agent_Home>/bin/agentca -f -c “node1,node2….” [-t -i oraInstloc -n CLUSTER_NAME ]

cluster1 (unknown)> ./agentca -f -c “cluster1 ,cluster2”

Stopping the agent using /opt/oracle/product/agent10g/bin/emctl  stop agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
Stopping agent … stopped.
Running agentca using /opt/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/opt/oracle/product/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/opt/oracle/product/agent10g/response_file RERUN=TRUE INV_PTR_LOC=/var/opt/oracle/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}
Perform – mode is starting for action: Configure
You can see the log file: /opt/oracle/product/agent10g/cfgtoollogs/oui/configActions2008-11-27_04-54-03-PM.log
ERROR: Unable to convert from “UTF-8” to “ISO8859-1” for NLS!
ÿÿÿÿÿÿÿÿ
The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /opt/oracle/oraInventory
‘UpdateNodeList’ was successful.
Starting the agent using /opt/oracle/product/agent10g/bin/emctl  start agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
Starting agent …………………………… started but not ready.

After creating clustered agent,  Cluster1 agent started OK. I could locate cluster1 under Targets on my grid control. But there is still an issue with cluster2 node

clsueter2(unknown)> ./emctl start agent
EM Configuration issue. /opt/oracle/product/agent10g/cluster2/sysman/config/emd.properties not found.

A closer look revealed that after converting agent into clustered config, default directories picked up for configuration are  different
For node 1 – $AGENT_HOME/cluster1/sysman/config/ & for node 2 $AGENT_HOME/cluster2/sysman/config/  changed from $AGENT_HOME/sysman/config/

I thought this could be resolved by 2 methods

• Copying the directory $AGENT_HOME/cluster1 as $AGENT_HOME/cluster2 , Edit “emd.properties“ and set EMD_URL as per machines

OK, I copied directories and started agent at cluster1, went OK. I then started agent at cluster2, went well as well but moments later Agent at Cluster 1 stopped. It revealed that they were sharing same port 3872 and only 1 agent could be active on that port. I then changed ports at emd.properties but still agents won’t start

cluster1/cluster2 > ./emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
Starting agent …………………………… failed.
Consult the log files in: /opt/oracle/product/agent10g/cluster1/sysman/log

I finally decided to : Run “agentca” at cluster2 node as well and oracle will create directory and files for node 2 at $AGENT_HOME/cluster2/sysman/config/

cluster1(unknown)> rm –Rf $AGENT_HOME/cluster2

Cluster2 (unknown)> ./agentca -f -c “cluster1, cluster2”

Cluster2 (unknown)> ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
—————————————————————
Agent Version     : 10.2.0.2.0
OMS Version       : 10.2.0.4.0
Protocol Version  : 10.2.0.2.0
—————————————————————
Agent is Running but Not Ready

Cluster2 (unknown)> ./emctl upload agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
—————————————————————
upload failed with ret=-10

Run following steps to push agent in “Agent is Running and Ready” status

$ ./emctl stop agent

Delete all files at $AGENT_HOME/sysman/emd/upload and $AGENT_HOME/sysman/emd/state
export AGENT_HOME=/opt/oracle/product/agent10g/
echo $AGENT_HOME
rm –Rf $AGENT_HOME/sysman/emd/upload/*.*
rm –Rf $AGENT_HOME/sysman/emd/state/*.*

Since this is clustered install , directories will be different for each node
rm –Rf /opt/oracle/product/agent10g/<cluster 1>/sysman/emd/upload/*.*
rm –Rf /opt/oracle/product/agent10g/<cluster 1>/sysman/emd/state/*.*
rm –Rf /opt/oracle/product/agent10g/<cluster 2>/sysman/emd/upload/*.*
rm –Rf /opt/oracle/product/agent10g/<cluster 2>/sysman/emd/state/*.*

$ ./emctl clearstate agent
$ ./emctl unsecure agent
$ ./emctl start agent
$ ./emctl upload agent
$ ./emctl secure agent

cluster2(unknown)> ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
—————————————————————
Last Reload       : 2008-12-04 16:44:26
Last successful upload                       : 2008-12-04 16:46:11
Total Megabytes of XML files uploaded so far :     0.58
Number of XML files pending upload           :        2
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    19.55%
Last successful heartbeat to OMS             : 2008-12-04 16:45:33
—————————————————————
Agent is Running and Ready

Both systems are communicating with grid now

We had to relocate one of the replication node to another location. During move, other master nodes had a huge queue build up i.e  more than 1.2 million waiting transactions recorded at deftran on 5 other nodes. When the server was finally back, transaction queues were replicated to original server but we hit performance problem.

I could see deftran had 0 entries at all 5 nodes but still taking 5 minutes to return result for “select count(*) from deftran”

select segment_name,sum(bytes)/1000000 “SIZE IN MB”
from dba_segments
where segment_name like ‘%DEF%’ group by segment_name order by 2 desc;

Showed DEF$_AQCALL is still more than 1GB in size and index on same table DEF$_TRANORDER in excess of 400MB.

I decided to truncate following system tables on all nodes to release space.

select count(*) from deferror;
COUNT(*)
———-
0
select count(*) from deftran;

COUNT(*)
———-
0

select count(*) from  system.DEF$_AQCALL;
select count(*) from  system.DEF$_AQERROR;
select count(*) from  system.DEF$_CALLDEST;
select count(*) from  system.DEF$_DEFAULTDEST;
select count(*) from  system.DEF$_ERROR;
select count(*) from  system.DEF$_ORIGIN;

truncate table system.DEF$_AQCALL;
truncate table system.DEF$_AQERROR;
truncate table system.DEF$_CALLDEST;
truncate table system.DEF$_DEFAULTDEST;
truncate table system.DEF$_ERROR;
truncate table system.DEF$_ORIGIN;

DEF$_AQCALL and DEF$_TRANORDER   dropped to 1 MB and queries are lightening fast.

DEFCALL Contains information about all deferred RPCs. Queries SYSTEM.DEF$_CALL table.

DEFCALLDEST Contains the destination database(s) for each deferred RPC in DEFCALL. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_CALLDEST, SYSTEM.DEF$_ERROR, and SYSTEM.REPCAT$_REPPROP.

DEFDEFAULTDEST Contains the default destinations for deferred RPCs. Queries SYSTEM.DEF$_DEFAULTDEST.

DEFERROR Contains error information for deferred calls that could not be applied at their destination. Queries DEF$_ERROR.

DEFERRORCOUNT Contains the count of errors for each destination. Queries SYSTEM.DEF$_ERROR.

DEFSCHEDULE Contains information about the scheduling of deferred jobs. Queries SYSTEM.DEF$_DESTINATION and SYS.JOB$.

DEFTRAN Contains information about all deferred calls. Queries SYSTEM.DEF$_CALL and SYS.USER$.

DEFTRANDEST Contains the destination database(s) for each deferred transaction. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_ERROR, SYSTEM.DEF$_CALLDEST, and SYSTEM.REPCAT$_REPPROP.

netstat displays the  contents  of  various  network-related  data structures in  depending on the options selected.

netstat  <option/s>

multiple options can be given at one time.

Options

-a – displays the state of all sockets.
-r – shows the system routing tables
-i – gives statistics on a per-interface basis.
-m – displays information from the network memory buffers. On Solaris, this shows statistics
         forSTREAMS
-p [proto] – retrieves statistics for the specified protocol
  -s – shows per-protocol statistics. (some implementations allow -ss to remove fileds with a value of 0 (zero) from the display.)
-D – display the status of DHCP configured interfaces.
-n do not lookup hostnames, display only IP addresses.
-d (with -i) displays dropped packets per interface.
-I [interface] retrieve information about only the specified interface.
-v be verbose

This shows the output on a Solaris machine who’s IP address is 192.168.1.11 with a default router at 192.168.1.1

Results and Solutions:

A.) Network availability

The command as above is mostly useful in troubleshooting network accessibility issues . When  outside network is not accessible from a machine check the following

1. if the default router ip  address is correct

2.  you can ping it from your machine.

3. If router address is incorrect  it can be changed  with route add  commnad . See man route  for more info .

route command examples:
$route add default <hostname>
$route add 192.0.2.32  <gateway_name>

If the router address is correct but still you can’t ping it  there may be some  network cable /hub/switch problem  and you have to try and eliminate the faulty component .

B.) Network Response

$ netstat -i
Name     Mtu     Net/Dest     Address     Ipkts     Ierrs     Opkts     Oerrs     Collis     Queue
lo0     8232     loopback     localhost     77814     0     77814     0     0     0
hme0     1500     server1     server1     10658566     3     4832511     0     279257     0

This option is used to diagnose the network problems when  the connectivity is there but  it is slow in response .

Values to look at:

    * Collisions (Collis)
    * Output packets (Opkts)
    * Input errors (Ierrs)
    * Input packets (Ipkts)

The above values will give information to workout

i.  Network collision rate as follows :

Network collision rate = Output collision counts / Output packets

Network-wide collision rate greater than 10 percent  will indicate

    *  Overloaded network,
    *  Poorly configured network,
    *  Hardware problems. 

ii.  Input packet error rate as follows :

Input Packet Error Rate = Ierrs / Ipkts.

If the input error rate is high (over 0.25 percent), the host is dropping packets. Hub/switch cables etc needs to be checked for potential problems.

C.  Network socket &  TCP Cconnection state

Netstat gives important   information about network socket and tcp state . This is very useful in
finding out the open , closed and  waiting network tcp connection .

Network states returned by  netstat are following :

     CLOSED               —-  Closed.  The socket  is  not  being used.
     LISTEN                 —-  Listening for incoming connections.
     SYN_SENT           —-  Actively trying to  establish  connection.
     SYN_RECEIVED  —- Initial synchronization of the connection under way.
     ESTABLISHED     —-  Connection has been established.
     CLOSE_WAIT      —-  Remote shut down; waiting  for  the socket to close.
     FIN_WAIT_1        —-  Socket closed; shutting  down  connection.
     CLOSING             —-  Closed,   then   remote   shutdown; awaiting acknowledgement.
     LAST_ACK          —-   Remote  shut  down,  then   closed ;awaiting acknowledgement.
     FIN_WAIT_2        —-  Socket closed; waiting for shutdown from remote.
     TIME_WAIT         —-  Wait after close for  remote  shutdown retransmission.

Example: #netstat -a

if  you see a lots of connections in FIN_WAIT state  tcp/ip parameters   have to be tuned  because the connections  are not being closed and they gets accumulating . After some time system may run out of resource . TCP parameter can be tuned to define a time out so that connections can be released and used by new connection.  

1. If you have a working EM grid console configuration, you can TAR a client configuration and clone it at other nodes.
2. Tar the working agent at node

$ cd /opt/oracle/product $ ls -l total 8 drwxr-xr-x  65 oracle9    oinstall      2048 Oct 10  2006 9.2.0 drwxrwx—   3 oracle9    oinstall        96 Nov 19 13:29 agent_10g $ tar –cvf agent.tar agent_10g

3. Set the ORACLE_HOME to /opt/oracle/product/agent10g< ?xml:namespace prefix = o />

$export ORACLE_HOME=/opt/oracle/product/agent10g

4. Run the installer as shown below –

$cd $ORACLE_HOME/oui/bin Or

$cd /opt/oracle/product/agent10g/oui/bin

$./runInstaller -clone -forceClone ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=agent10g -noconfig –silent

5. Run the root.sh as root user

/opt/oracle/product/agent10g/agent10g/root.sh

6. Amend the REPOSITORY_URL and emdWalletSrcUrl parameters in the <AGENT_HOME>/sysman/config/emp.properties configuration file to make the agent point to right grid server

7. Run the agent configuration assistant

$cd $ORACLE_HOME/bin or

$cd /opt/oracle/product/agent10g/bin

$./agentca -f

8. Secure the agent

$./emctl secure agent <password>

9. Start the agent, if not already

$./emctl start agent

I installed 10g grid console agent using  notes

Locate the Database SID from HPUNIX-/etc/oratab or SunOS-/var/opt/oratab file

Goto –>Main Grid Console  -> Click on Databases  -> Enter “Name of Database” in “Search” and click “GO”

Alternatively click on “Targets” to list available databases,listeners

Click on “Configure” button and enter details for DBSNMP user

Click on “TEST Connection” to see message “”The connection test was successful”

vmstat –  vmstat reports virtual memory statistics of   process, virtual memory, disk, trap, and CPU activity.

On multicpu systems , vmstat averages the number of CPUs  into  the  output. For per-process statistics .Without options, vmstat displays a one-line summary  of  the  virtual memory activity since the system was booted.

Basic synctax is vmstat  <options>   interval  count

option – let you specify the type of information needed such as paging  -p , cache   -c ,.interrupt -i  etc.

if no option is specified  information about   process , memory , paging , disk ,interrupts & cpu  is displayed  .

interval – is time period in seconds between two samples . vmstat   4  will give data at each 4 seconds interval.

count  – is the number of times the data is needed . vmstat 4   5   will give data at 4 seconds interval   5

             times.

The following command displays a summary of what the  system is doing every five seconds.

     example% vmstat 5

     procs  memory          page             disk      faults        cpu
     r b w swap  free re mf pi p fr de sr s0 s1 s2 s3  in  sy  cs us sy id
     0 0 0 11456 4120 1  41 19 1  3  0  2  0  4  0  0  48 112 130  4 14 82
     0 0 1 10132 4280 0   4 44 0  0  0  0  0 23  0  0 211 230 144  3 35 62
     0 0 1 10132 4616 0   0 20 0  0  0  0  0 19  0  0 150 172 146  3 33 64
     0 0 1 10132 5292 0   0  9 0  0  0  0  0 21  0  0 165 105 130  1 21 78

The fields of vmstat’s display are
    procs
            r     in run queue
            b     blocked for resources I/O, paging etc.
           w     swapped

    memory (in Kbytes)
             swap –  amount  of  swap   space   currently   available               
             free   – size of the free list

    page ( in units per second).
          re    page reclaims –  see  -S  option  for  how  this field is modified.
          mf    minor faults –  see  -S  option  for  how    this field is modified.
          pi    kilobytes paged in
          po    kilobytes paged out
          fr    kilobytes freed
          de    anticipated short-term memory shortfall (Kbytes)
          sr    pages scanned by clock algorithm

    disk  ( operations per second )
          There are  slots for up to four disks, labeled with a single letter and number.
          The letter indicates  the  type  of  disk  (s = SCSI, i = IPI, etc) . The number is 
          the logical unit number.

    faults
           in    (non clock) device interrupts
          sy    system calls
          cs    CPU context switches

    cpu –   breakdown of percentage usage of CPU  time.  On multiprocessors  this is an a
               verage across all processors.
          us    user time
          sy    system time
          id    idle time

Results and Solutions:

A.   CPU issues:

Following columns has to be watched to determine if there is any cpu issue

Processes in the run queue (procs r)
User time (cpu us)
System time (cpu sy)
Idle time (cpu id)

     procs      cpu
     r b w    us sy  id
     0 0 0    4  14  82
     0 0 1    3  35  62
     0 0 1    3  33  64
     0 0 1    1  21  78

Problem symptoms:
1.) If the number of processes in run queue (procs r) are consistently greater than the number of CPUs on the system it will slow down system as there are more processes then available CPUs .
2.) if  this number is more than four times the number of available CPUs in the system then system is facing shortage of cpu power and will greatly slow down the processess on the system.
3.) If  the idle time (cpu id) is consistently 0 and if the system time (cpu sy) is double the user time (cpu us)  system is facing shortage of CPU resources.
Resolution :
Resolution to these kind of issues involves tuning of application procedures  to make efficient use of cpu  and as a last resort increasing the cpu power or adding more cpu to the system.  

B.   Memory Issues:
Memory bottlenecks are determined by the scan rate (sr) . The scan rate is the pages scanned by the clock algorithm per second. If the scan rate (sr) is continuously over 200 pages per second then there is a memory shortage.
Resolution :
1. Tune the applications & servers to make  efficient use of memory and cache.
2. Increase system memory .
3. Implement priority paging in s in pre solaris 8 versions by adding line “set priority paging=1” in
    /etc/system. Remove this line if upgrading from Solaris 7 to 8 & retaining old /etc/system file.

./runInstaller
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.11 or B.11.23.    Actual B.11.11
Passed

Checking swap space: must be greater than 500 MB.   Actual 32768 MB    Passed
Checking temp space: 48 MB available, 250 MB required.    Failed <<<<

Set a TEMP space variable to point at /u02/stage/tmpdir volume
$ echo $TMPDIR
/u02/stage/tmpdir
$ echo $TMP
/u02/stage/tmpdir
TMPDIR & $TMP Didn’t WORK

$ export TEMP=/u02/stage/tmpdir — Worked

$ ./runInstaller
Starting Oracle Universal Installer…

Checking installer requirements…

Checking operating system version: must be B.11.11 or B.11.23.    Actual B.11.11   Passed

Checking swap space: must be greater than 500 MB.   Actual 32768 MB    Passed
Checking Temp space: must be greater than 250 MB.   Actual 198722 MB    Passed

All installer requirements met.

 iostat   reports terminal and disk  I/O  activity and  CPU utilization.  The first line of output is for the  time period  since boot  &  each subsequent line is for  the  prior  interval . Kernel maintains  a number of counters to keep track of  the  values.

iostat’s activity class options default  to  tdc  (terminal,  disk, and CPU). If any other option/s are specified,  this  default is completely overridden i.e.  iostat -d will report only statistics about the disks.

iostat  <options>   interval  count

option – let you specify the device for which information is needed like disk , cpu or terminal. (-d , -c , -t  or -tdc ) .  x options gives the extended statistics .

interval –  is time period in seconds between two samples . iostat  4  will give data at each 4 seconds interval.

count  – is the  number of times the data is needed .  iostat 4 5   will give data at 4 seconds interval   5 times

 $ iostat -xtc 5 2
                          extended disk statistics       tty         cpu
     disk r/s  w/s Kr/s Kw/s wait actv svc_t  %w  %b  tin tout us sy wt id
     sd0   2.6 3.0 20.7 22.7 0.1  0.2  59.2   6   19   0   84  3  85 11 0
     sd1   4.2 1.0 33.5  8.0 0.0  0.2  47.2   2   23
     sd2   0.0 0.0  0.0  0.0 0.0  0.0   0.0   0    0
     sd3  10.2 1.6 51.4 12.8 0.1  0.3  31.2   3   31

[oracle@]$ iostat -dn 5  | grep data
Device:                  rBlk_nor/s   wBlk_nor/s   rBlk_dir/s   wBlk_dir/s   rBlk_svr/s   wBlk_svr/s
netapp:/vol/test_data         0.02         0.15         0.00         0.00      5985.39      1694.18
netapp:/vol/test_c_data       0.00         0.00         0.00         0.00      3975.35      2160.74
netapp:/vol/streams_data      0.05         0.43         0.00         0.00       230.63        45.43
netapp:/vol/test_data         0.00         0.00         0.00         0.00     70004.41        59.32
netapp:/vol/test_c_data       0.00         0.00         0.00         0.00       160.32       102.61
netapp:/vol/streams_data      0.00         0.00         0.00         0.00     12710.22     20754.31

The fields have the following meanings:
      disk    name of the disk
      r/s     reads per second
      w/s     writes per second
      Kr/s    kilobytes read per second
      Kw/s    kilobytes written per second
      wait    average number of transactions waiting for service (Q length)

      actv    average number of transactions  actively being serviced (removed  from  the queue but not yet
              completed)
      %w      percent of time there are transactions  waiting for service (queue non-empty)
      %b      percent of time the disk is busy  (transactions in progress)

Find files older than 5 days:    find . -mtime +5 -print | xargs ls -l | more
Remove files older than 5 days :  find . -mtime +5 -print | xargs rm

List files named “trust.p12” with their attributes

$ find . -name “*trust.p12” -type f -ls
738579    4 -rw-rw-r–   1 was61    was61        1586 Oct 23 04:36 ./dmgr/etc/trust.p12
738623    4 -rw-rw-r–   1 was61    was61        2730 Oct 23 02:02 ./dmgr/config/cells/Cell/trust.p12
738625    4 -rw-rw-r–   1 was61    was61        2730 Oct 23 02:02 ./dmgr/config/cells/Cell/nodes/Node01/trust.p12
1098642    4 -rw-rw-r–   1 was61    was61         850 Jul 13  2010 ./Node/etc/trust.p12
1098644    4 -rw-rw-r–   1 was61    was61        2730 Oct  7  2010 ./Node/config/cells/Cell/trust.p12
1098647    4 -rw-rw-r–   1 was61    was61        2730 Oct  7  2010 ./Node/config/cells/Cell/nodes/Node01/trust.p12

Print the number of blocks used by each directory

find . -type d -exec du -s {} \;

Find directory size recursively

$du -h –max-depth=10

8.1G    ./current/ABLXPORA01
153M    ./current/BAK/ABMWPSQL01/BDY_TVP_P

$ du -a | sort -rn | head
9646324 .
8532176 ./IBM
4008132 ./IBM/WebSphere
3753856 ./IBM/WebSphere/AppServer
3321980 ./IBM/HTTPServer
3003896 ./IBM/HTTPServer/Plugins

$ du -sk * | sort -nk 1
228     cdump
2468    stage
3924    metadata
5328    alert
286480  trace
6757680 incident

Print names of all files over 5,000 blocks (2,560,000) bytes.

du -sk * | sort -nk 1 | pg

List and sort out the files per their size

find $ORACLE_BASE -size +2000 -exec ls -s {} \; | sort -nr | more

Look for which big files have filled up a filesystem recently

find . -size +20000 -mtime -10 -ls  (10 here is days)

Find files which were modified either (<)  less than 1 week ago but more (>) than 2 weeks ago

find . \( -mtime +14 -o -mtime -7 \) -ls

Find and delete files older than 5 days

-rw-r—– 1 oracle oracle 2620416 Aug 30 23:16 1_2114_692896370.arc
-rw-r—– 1 oracle oracle 2620416 Sep  1 23:28 1_2161_692896370.arc
-rw-r—– 1 oracle oracle 2624512 Feb 14 00:18 1_21301_700768645.arc

find . -type f -mtime +15 -exec rm -f {} \;

-rw-r—– 1 oracle oracle 2624512 Feb 14 00:18 1_21301_700768645.arc

Find all files modified in last 10 days and  print them as <Size> <Filename>, I have used grep again to list only log files

$ find /opt -size +20000 -mtime -10 -ls |  awk ‘{print $7 ” ”  $11}’ | grep log
104857600 /opt/filestores/com.ibm.ws.sib/localhost_Node01.dev_server_member1-PRPC_Bus-1A01E8290042E159/log/Log
104857600 /opt/filestores/com.ibm.ws.sib/localhost_Node01.dev_server_member2-PRPC_Bus-DF5E2FA284B7341C/log/Log
147066880 /opt/logs/dev_server_member2/native_stderr.log

Find all files containing a string in filename only

$du -a | grep “string”

Find all files recursively with a “hostName” string:

grep -H -r “hostName=” /opt |  cut -d: -f1
/opt/IBM/WebSphere/localhost_Cell/nodes/localhost_Node01/serverindex.xml
/opt/IBM/WebSphere/localhost_Cell/nodes/localhost_Manager/serverindex.xml

Look for word 500 and print lines on screen  :

egrep -in “500” $HOME/today_access.log

Search for word “500” in files at /opt directory

find /opt | xargs grep -l 500

find . | xargs grep 'string'

I need to return only name of file from “ls -l”

-rwxr-xr-x 1 was61 web  4970 Jun 30  2009 startManager.sh
-rwxr-xr-x 1 was61 web  5026 Jun 30  2009 startNode.sh
ls -la | awk ‘{print $9}’
startManager.sh
startNode.sh

Get kill PID of sessions  : ps -ef | grep java | grep dev_server_member2 | awk ‘{print $2}’

This command will backup trace files and then delete them

tar -czvf /mnt/backup/Traces_`date +%Y%m%d_%H%M`.tgz   /opt/app/oracle/diag/rdbms/prod/trace/*.tr*   &&  find *.tr* -delete

Shell script to Find Files Recursively with a “string”

#!/bin/bash
#
# Author: Sagar PATIL
echo "Enter the FullPackage DIR path[/opt/IBM/WebSphere/AppServer/profiles/Profile01/Dmgr/config/cells......] "
read PKG_DIR
echo "Enter the Source Pattern to be replaced "
read SS
grep -H -r ${SS} ${PKG_DIR} |  cut -d: -f1

Shell script to Find & Replace a “string” Recursively

#!/bin/bash
# Author: Sagar PATIL

echo "Enter the FullPackage DIR path[/opt/IBM/WebSphere/AppServer/profiles/Profile01/Dmgr/config/cells......] "
read PKG_DIR
echo "Enter the Source Pattern to be replaced "
read SS
echo "Enter the Target Pattern "
read TS
list=`find $PKG_DIR -type f -name '*.xml'`  # I am only searching for files with extn xml, to search all files please remove name '*.xml'
for file in ${list}
do
#in the below command colon is a separator
sed "s:"${SS}":"${TS}":g" ${file} > /tmp/temp.txt
echo "File " ${file}
echo "Source Pattern " ${SS}
echo "Target Pattern " ${TS}
mv /tmp/temp.txt ${file}
done

Note: You must be logged in as the root user, because root owns the OCR files.  Make sure there is a recent copy of the OCR file before making any changes: ocrconfig ­showbackup

If there is not a recent backup copy of the OCR file, an export can be taken for the current OCR file.

Use the following command to generate an export of the online OCR file:
ocrconfig ­export <OCR export_filename> -s online
If you should need to recover using this file, the fol owing command can be used:
ocrconfig import <OCR export_filename>

1. To add an OCR device:
To add an OCR device, provide the ful path including file name.

ocrconfig -replace ocr <filename>
To add an OCR mirror device, provide the ful path including file name.

ocrconfig -replace ocrmirror <filename>
2. To remove an OCR device:
To remove an OCR device:

ocrconfig -replace ocr
To remove an OCR mirror device

ocrconfig -replace ocrmirror
3. To replace or move the location of an OCR device:
To replace the OCR device with <filename>, provide the ful path including file name.

ocrconfig -replace ocr <filename>
To replace the OCR mirror device with <filename>, provide the ful path including file name.

ocrconfig -replace ocrmirror <filename>

Example moving OCR file from OCFS to raw devices
The OCR disk must be owned by root, must be in the oinstal group, and must have permissions set to 640.
In this example the OCR files are located in the ocfs2 file system:
/ocfs2/ocr1
/ocfs2/ocr2

Create raw device files of at least 100 MB. In this example the new OCR file wil be on the fol owing devices:
/dev/raw/raw1
/dev/raw/raw2
Once the raw devices are created, use the dd command to zero out the device and make sure no data is written
to the raw devices:
dd if=/dev/zero of=/dev/raw/raw1
dd if=/dev/zero of=/dev/raw/raw2

Note: Use UNIX man pages for additional information on the dd command.Now you are ready to move/replace the OCR file to the new storage location.

Move/Replace the OCR device
ocrconfig -replace ocr /dev/raw/raw1
Add /dev/raw/raw2 as OCR mirror device

ocrconfig -replace ocr /dev/raw/raw2
Example of adding an OCR device file
If you have upgraded your environment from a previous version, where you only had one OCR device file, you can
use the fol owing step to add an additional OCR file.
In this example a second OCR device file is added:
Add /dev/raw/raw2 as OCR mirror device

ocrconfig -replace ocr /dev/raw/raw2
ADD/DELETE/MOVE Voting Disk
Note: crsctl votedisk commands must be run as root
Note: Only use the -force flag when CRS is down
Shutdown the Oracle Clusterware (crsctl stop crs as root) on al nodes before making any modification to the
voting disk. Determine the current voting disk location using:
crsctl query css votedisk
Take a backup of al voting disk:

dd if=voting_disk_name of=backup_file_name
Note: Use UNIX man pages for additional information on the dd command.  The following can be used to restore the voting disk from the backup file created.

dd if=backup_file_name of=voting_disk_name
1. To add a Voting Disk, provide the full path including file name.:

crsctl add css votedisk <RAW_LOCATION> -force
2. To delete a Voting Disk, provide the full path including file name.:

crsctl delete css votedisk <RAW_LOCATION> -force
3. To move a Voting Disk, provide the full path including file name.:

crsctl delete css votedisk <OLD_LOCATION> ­force
crsctl add css votedisk <NEW_LOCATION> ­force
After modifying the voting disk, start the Oracle Clusterware stack on al nodes

crsctl start crs
Verify the voting disk location using

crsctl query css votedisk

1> Example : Moving Voting Disk from OCFS to raw devices. The voting disk is a partition that Oracle Clusterware uses to verify cluster node membership and status.
The voting disk must be owned by the oracle user, must be in the dba group, and must have permissions set to 644. Provide at least 20 MB disk space for the voting disk.
In this example the Voting Disks are located in the ocfs2 file system:
/ocfs2/voting1
/ocfs2/voting2
/ocfs2/voting3
Create raw device files of at least 20 MB. In this example the new voting disks wil be on the fol owing devices:
/dev/raw/raw3
/dev/raw/raw4
/dev/raw/raw5

Once the raw devices are created, use the dd command to zero out the device and make sure no data is written to the raw devices:
dd if=/dev/zero of=/dev/raw/raw3
dd if=/dev/zero of=/dev/raw/raw4
dd if=/dev/zero of=/dev/raw/raw5

Now you are ready to move/replace the voting disks to the new storage location.

To move a Voting Disk to new storage location:
crsctl delete css votedisk /ocfs2/voting1 ­force
crsctl add css votedisk /dev/raw/raw3 ­force
crsctl delete css votedisk /ocfs2/voting2 ­force
crsctl add css votedisk /dev/raw/raw4 ­force
crsctl delete css votedisk /ocfs2/voting3 ­force
crsctl add css votedisk /dev/raw/raw5 ­force

2> Example of adding Voting Disks
If you have upgraded your environment from a previous version, where you only had one voting disk, you can use
the fol owing steps to add additional voting disk.
In this example 2 additional Voting Disks are added:

crsctl add css votedisk /dev/raw/raw4 ­force
crsctl add css votedisk /dev/raw/raw5 ­force
After modifying the voting disk, start the Oracle Clusterware stack on al nodes

crsctl start crs
Verify the voting disk location using
crsctl query css votedisk

References
Note 390880.1 – OCR Corruption after Adding/Removing voting disk to a cluster when CRS stack is running

Step by Step instructions on how to remove temp nodes from RAC cluster. Step by step instruction on how to verify removal of temp nodes.

REMOVAL OF CLUSTERING AFTER FAILOVER

1.shutdown the instances prod1,prod2 and then do the following.

2.Remove all the devdb entries for devdb or tempracsrv3,tempracsrv4 in tnsnames.ora

In both the servers—i.e. prodracsrv1,prodracsrv2.

3.Remove the following entries from init.ora in prodracsrv1,prodracsrv2

*.log_archive_config=’dg_config=

(PROD,DEVDB)’

*.log_archive_dest_2=’service=DEVDB

valid_for=(online_logfiles,primary_role)

db_unique_name=DEVDB’

*.standby_file_management=auto

*.fal_server=’DEVDB’

*.fal_client=’PROD’

*.service_names=’PROD’

4.After this Your PROD Database is Ready after failover .

Step by Step instructions on how to fail RAC databases over from temp nodes to prod nodes. Includes step by step instructions on how to verify the failover from temp nodes to prod nodes. Step by Step instructions on how to test RAC database connectivity after failover.

FAILOVER

Performing a failover in a Data Guard configuration converts the standby database into the production database. The following sections describe this

Manual Failover

Manual failover is performed by the administrator directly through the Enterprise Manager graphical user interface, or the Data Guard broker command-line interface (DGMGRL), or by issuing SQL*Plus statements. The sections below describe the relevant SQL*Plus commands.

Simulation of Failover :-

Shutdown both the instances devdb1 ,devdb2(tempracsrv3,tempracsrv4) by connecting / as sysdba from command line And issuing the following command

SQL>shutdown abort..

Manual Failover to a Physical Standby Database(in PROD_PRODRACSRV1)

Use the following commands to perform a manual failover of a physical standby Database:

1. Initiate the failover by issuing the following on the target standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Note: Include the FORCE keyword to ensure that the RFS processes on the standby database will fail over without waiting for the network Connections to time out through normal TCP timeout processing before Shutting down.

2. Convert the physical standby database to the production role:

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. If the standby database was never opened read-only since the last time it was Started, then open the new production database by issuing the following Statement:

ALTER DATABASE OPEN;

If the physical standby database has been opened in read-only mode since the

last time it was started, shut down the target standby database and restart it:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

Note: In rare circumstances, administrators may wish to avoid waiting for the standby database to complete applying redo in the current standby redo log file before performing the failover. (note: use of Data Guard real-time apply will avoid this delay by keeping apply up to date on the standby database). If so desired, administrators may issue the ALTER DATABASE ACTIVATE STANDBY DATABASE statement to perform an immediate failover.

This statement converts the standby database to the production database, creates a new resetlogs branch, and opens the database. However, because this statement will cause any un-applied redo in the standby redo log to be lost, Oracle recommends you only use the failover procedure described in the above steps to perform a failover.

Oracle 10g R2 RAC Installation for PROD Nodes:
Step by Step instructions for installing Oracle 10g R2 RAC installation. The procedures will provide STEP By STEP guide you for installing two nodes (prodracsrv1and prodracsrv2) and adding to the existing RAC cluster(Configuring Failover).

10g RAC Installation (Part-II Clusterware & Database Installation)

1. Install Oracle Clusterware

Mount the Clusterware dvd in the prodracsrv1 and run the runInstaller

After downloading, as the oracle user on prodracsrv1, execute

1. Welcome: Click on Next.

2. Specify Inventory directory and credentials:

o Enter the full path of the inventory directory:

/u01/app/oracle/oraInventory.

o Specify Operating System group name: oinstall.

3. Specify Home Details:

o Name: OraCrs10g_home

o /u01/app/oracle/product/10.2.0/crs_1

4. Product-Specific Prerequisite Checks:

o Ignore the warning on physical memory requirement.

5. Specify Cluster Configuration: Click on Add.

o Public Node Name: prodracsrv2.mycorpdomain.com

o Private Node Name: prodracsrv2-priv.mycorpdomain.com

o Virtual Host Name: prodracsrv2-vip.mycorpdomain.com

6. Specify Network Interface Usage:

o Interface Name: eth0

o Subnet: 192.168.2.0

o Interface Type: Public

o Interface Name: eth1

o Subnet: 10.10.10.0

o Interface Type: Private

7. Specify Oracle Cluster Registry (OCR) Location: Select External Redundancy.

For simplicity, here you will not mirror the OCR. In a production environment,

you may want to consider multiplexing the OCR for higher redundancy.

o Specify OCR Location: /u01/ocr_config

8. Specify Voting Disk Location: Select External Redundancy.

Similarly, for simplicity, we have chosen not to mirror the Voting Disk.

o Voting Disk Location: /u01/votingdisk

9. Summary: Click on Install.

10. Execute Configuration scripts: Execute the scripts below as the root user

sequentially, one at a time. Do not proceed to the next script until the current

script completes.

o Execute /u01/app/oracle/oraInventory/orainstRoot.sh on prodracsrv1.

o Execute /u01/app/oracle/oraInventory/orainstRoot.sh on prodracsrv2.

o Execute /u01/app/oracle/product/10.2.0/crs_1/root.sh on prodracsrv1.

o Execute /u01/app/oracle/product/10.2.0/crs_1/root.sh on prodracsrv2.

The root.sh script on prodracsrv2 invoked the VIPCA automatically but it failed with the

error “The given interface(s), “eth0″ is not public. Public interfaces should be

used to configure virtual IPs.” As you are using a non-routable IP address

(192.168.x.x) for the public interface, the Oracle Cluster Verification Utility

(CVU) could not find a suitable public interface. A workaround is to run VIPCA

manually.

11. As the root user, manually invokes VIPCA on the second node.

# /u01/app/oracle/product/10.2.0/crs_1/bin/vipca

Welcome: Click on Next.

Network Interfaces: Select eth0.

Virtual IPs for cluster nodes:

o Node name: prodracsrv1

o IP Alias Name: prodracsrv1-vip

o IP address: 192.168.2.31

o Subnet Mask: 255.255.255.0

o Node name: prodracsrv2

o IP Alias Name: prodracsrv2-vip

o IP address: 192.168.2.32

o Subnet Mask: 255.255.255.0

Summary: Click on Finish.

Configuration Assistant Progress Dialog: After the configuration has completed,

Click on OK.

Configuration Results: Click on Exit.

Return to the Execute Configuration scripts screen on prodracsrv1 and click on OK.

Configuration Assistants: Verify that all checks are successful. The OUI does a

Clusterware post-installation check at the end. If the CVU fails, correct the

Problem and re-run the following command as the oracle user.

prodracsrv1-> /u01/app/oracle/product/10.2.0/crs_1/bin/cluvfy stage -post crsinst -n prodracsrv1, prodracsrv2

23. Performing post-checks for cluster services setup

24.

25. Checking node reachability…

26. Node reachability check passed from node “prodracsrv1”.

27.

28. Checking user equivalence…

29. User equivalence check passed for user “oracle”.

30.

31. Checking Cluster manager integrity…

32.

33. Checking CSS daemon…

34. Daemon status check passed for “CSS daemon”.

35.

36. Cluster manager integrity check passed.

37.

38. Checking cluster integrity…

39.

40. Cluster integrity check passed

41.

42. Checking OCR integrity…

43.

44. Checking the absence of a non-clustered configuration…

45. All nodes free of non-clustered, local-only configurations.

46.

47. Uniqueness check for OCR device passed.

48.

49. Checking the version of OCR…

50. OCR of correct Version “2” exists.

51.

52. Checking data integrity of OCR…

53. Data integrity check for OCR passed.

54.

55. OCR integrity check passed.

56.

57. Checking CRS integrity…

58.

59. Checking daemon liveness…

60. Liveness check passed for “CRS daemon”.

61.

62. Checking daemon liveness…

63. Liveness check passed for “CSS daemon”.

64.

65. Checking daemon liveness…

66. Liveness check passed for “EVM daemon”.

67.

68. Checking CRS health…

69. CRS health check passed.

70.

71. CRS integrity check passed.

72.

73. Checking node application existence…

74.

75. Checking existence of VIP node application (required)

76. Check passed.

77.

78. Checking existence of ONS node application (optional)

79. Check passed.

80.

81. Checking existence of GSD node application (optional)

82. Check passed.

83.

84. Post-check for cluster services setup was successful.

85. End of Installation: Click on Exit.

2. Install Oracle Database 10g Release 2

After mounting database 10g R2 DVD run the installer

prodracsrv1-> ./runInstaller

1. Welcome: Click on Next.

2. Select Installation Type:

o Select Enterprise Edition.

3. Specify Home Details:

o Name: OraDb10g_home1

o Path: /u01/app/oracle/product/10.2.0/db_1

4. Specify Hardware Cluster Installation Mode:

o

Select the “Cluster Install” option and make sure both RAC nodes are selected, the click the “Next” button

o Select the “Install database Software only” option, then click the “Next” button.

Wait while the prerequisite checks are done. If you have any failures correct them and retry the tests before clicking the “Next” button.

7. Select the “Install database Software only” option, then click the “Next” button.

8. On the “Summary” screen, click the “Install” button to continue.

9. Wait while the database software installs.

10. Once the installation is complete, wait while the configuration assistants run.

11. Execute the “root.sh” scripts on both nodes, as instructed on the “Execute Configuration scripts” screen, then click the “OK” button.

12. When the installation is complete, click the “Exit” button to leave the installer.

Adding to the cluster

RAC Physical Standby for a RAC Primary

Overview……………………………………………………………………………………………….2

Task 1: Gather Files and Perform Back Up…………………………………………..3

Task 2: Configure Oracle Net SERVICES on the Standby……………………3

Task 3: Create the Standby Instances and Database………………………………4

Task 4: Configure The Primary Database For Data Guard……………………9

Task 5: Verify Data Guard Configuration……………………………………………10

the database unique name of the RAC database as DEVDB. The instance names of the two RAC instances are DEVDB1 (on node DEVDB_tempracsrv3) and DEVDB2 (on node DEVDB_tempracsrv4). The database unique name of the RAC standby database is PROD, and the two standby instance names are PROD1 (on node PROD_prodracsrv1) and PROD2 (on node PROD_prodracsrv2).

This document includes the following tasks:

• Task 1: Gather Files and Perform Back Up

• Task 2: Configure Oracle Net on the Standby

• Task 3: Create the Standby Instances and Database

• Task 4: Configure the Primary Database for Data Guard

• Task 5: Verify Data Guard Configuration

This document assumes that the following conditions are met:

• The primary RAC database is in archivelog mode

Creating a RAC Physical Standby for a RAC Primary

• The primary and standby databases are using a flash recovery area.

• The standby RAC hosts have existing Oracle software installation.(prodracsrv1,prodracsrv2)…..

• Oracle Managed Files (OMF) is used for all storage.

TASK 1: GATHER FILES AND PERFORM BACK UP

1. On tempracsrv3 node, create a staging directory. For example:

[oracle@DEVDB_tempracsrv3 oracle]$ mkdir -p /opt/oracle/stage

2. Create the same exact path on one of the standby hosts:

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p /opt/oracle/stage

3. On the tempracsrv3 node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. For example:

SQL> CREATE PFILE=’/opt/oracle/stage/initDEVDB.ora’ FROM SPFILE;

4. On the tempracsrv3 node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory. For example:

[oracle@DEVDB_host1 stage]$ rman target /

RMAN> BACKUP DEVICE TYPE DISK FORMAT ‘/opt/oracle/stage/%U’ DATABASE PLUS ARCHIVELOG;

RMAN> BACKUP DEVICE TYPE DISK FORMAT ‘/opt/oracle/stage/%U’ CURRENT CONTROLFILE FOR STANDBY;

5. Place a copy of the listener.ora, tnsnames.ora, and sqlnet.ora files into the staging directory. For example:

[oracle@DEVDB_tempracsrv3 oracle]$ cp $ORACLE_HOME/network/admin/*.ora /opt/oracle/stage

6. Copy the contents of the staging directory on the RAC primary node to the standby node on which the staging directory was created on in step 2. For example:

[oracle@DEVDB_host1 oracle]$ scp /opt/oracle/stage/* \

oracle@PROD_prodracsrv1:/opt/oracle/stage

and from there copy all the dbf’s the current logfiles,standby controlfile to the according locations(Note the location should be the same location as it was in the primary).

TASK 2: CONFIGURE ORACLE NET SERVICES ON THE STANDBY

1. Copy the listener.ora, tnsnames.ora, and sqlnet.ora files from the staging directory on the standby host to the $ORACLE_HOME/network/admin directory on all standby hosts.

2. Modify the listener.ora file each standby host to contain the VIP address of that host.

Creating a RAC Physical Standby for a RAC Primary

3. Modify the tnsnames.ora file on each node, including the primary RAC nodes and standby RAC nodes, to contain all primary and standby net service names. You should also modify the Oracle Net aliases that are used for the local_listener and remote_listener parameters to point to the listener on each standby host. In this example, each tnsnames.ora file should contain all of the net service names in the following table:

Example Entries in the tnsnames.ora Files

4. Start the standby listeners on all standby hosts.

TASK 3: CREATE THE STANDBY INSTANCES AND DATABASE

1. To enable secure transmission of redo data, make sure the primary and standby databases use a password file, and make sure the password for the SYS user is identical on every system. For example:

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwPROD password=oracle

2. Copy and rename the primary database PFILE from the staging area on all standby hosts to the $ORACLE_HOME/dbs directory on all standby hosts. For example:

[oracle@PROD_host1 stage]$ cp initDEVDB1.ora $ORACLE_HOME/dbs/initPROD1.ora

3. Modify the standby initialization parameter file copied from the primary node to include Data Guard parameters as illustrated in the following table:

Creating a RAC Physical Standby for a RAC Primary

Initialization Parameter Modifications

5. Connect to the standby database on one standby host, with the standby in the IDLE state, and create an SPFILE in the standby DATA disk group:

SQL> CREATE SPFILE=’+DATA/PROD/spfilePROD.ora’ FROM PFILE=’?/dbs/initPROD.ora’;

6. In the $ORACLE_HOME/dbs directory on each standby host, create a PFILE that is named initoracle_sid.ora that contains a pointer to the SPFILE. For example:

[oracle@PROD_prodracsrv1 oracle]$ cd $ORACLE_HOME/dbs

[oracle@PROD_prodracsrv1 dbs]$ echo spfilePROD.ora > initPROD1.ora

7. Create the dump directories on all standby hosts as referenced in the standby initialization parameter file. For example:

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/bdump

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/cdump

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/udump

[oracle@PROD_prodracsrv1 oracle]$ mkdir -p $ORACLE_BASE/admin/PROD/adump

8. After setting up the appropriate environment variables on each standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the standby database instance on the standby host that has the staging directory, without mounting the control file.

SQL> STARTUP NOMOUNT

9. From the standby host where the standby instance was just started, duplicate the primary database as a standby into the ASM disk group. For example:

$ rman target sys/oracle@DEVDB auxiliary /

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

10. Connect to the standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as

the primary database online logs. The recommended number of standby redo logs is:

(maximum # of logfiles +1) * maximum # of threads

This example uses two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

.

11. On only one standby host (and this is your designated Redo Apply instance), start managed recovery and real-time apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

12. On either node of the standby cluster, register the standby database and the database instances with the Oracle Cluster Registry (OCR) using the Server Control (SRVCTL) utility. For example:

$ srvctl add database -d PROD –o /u01/app/oracle/product/10.2.0/db_1

$ srvctl add instance -d PROD -i PROD1 -n PROD_prodracsrv1

$ srvctl add instance -d PROD -i PROD2 -n PROD_prodracsrv2

The following are descriptions of the options in these commands:

The -d option specifies the database unique name (DB_UNIQUE_NAME) of the database.

The -i option specifies the database insance name.

The -n option specifies the node on which the instance is running.

The -o option specifies the Oracle home of the database.

TASK 4: CONFIGURE THE PRIMARY DATABASE FOR DATA GUARD

1. Configure the primary database initialization parameters to support both the primary and standby roles.

*.log_archive_config=’dg_config=(PROD,DEVDB)’

*.log_archive_dest_2=’service=PROD

valid_for=(online_logfiles,primary_role)

db_unique_name=PROD’

*.standby_file_management=auto

*.fal_server=’PROD’

*.fal_client=’DEVDB’

*.service_names=DEVDB

Note that all the parameters listed above can be dynamically modified with the exception of the standby role parameters log_file_name_convert and db_file_name_convert. It is recommended to set the parameters with “scope=spfile” so that they can be put into effect upon the next role change.

2. Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. Because this example has two online redo logs for each thread, three standby redo logs are required for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

TASK 5: VERIFY DATA GUARD CONFIGURATION

1. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

2. On the primary database, issue the following SQL statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. On the standby database, query the V$ARCHIVED_LOG view to verify that the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Now we have setup a Failover instance for RAC on prodracsrv1,prodracsrv2.

Oracle 10g R2 RAC Installation for Temp Nodes:
Step by Step instructions for installing Oracle 10g R2 RAC installation. The procedures will provide STEP by STEP guide you for installing two nodes (tempracsrv3 and tempracsrv4). Installation on this phase includes documentation on how to verify the installation and configuration is installed correctly. Step by Step instructions on creating RAC database, importing schemas from oracle 9i database into new databases, and testing database and node connectivity for this phase of the step by step instructions.

10g RAC Installation (Part-II Clusterware & Database Installation)

1. Install Oracle Clusterware

Mount the Clusterware dvd in the tempracsrv3 and run the runInstaller

After downloading, as the oracle user on tempracsrv3, execute

1. Welcome: Click on Next.

2. Specify Inventory directory and credentials:

o Enter the full path of the inventory directory:

/u01/app/oracle/oraInventory.

o Specify Operating System group name: oinstall.

3. Specify Home Details:

o Name: OraCrs10g_home

o /u01/app/oracle/product/10.2.0/crs_1

4. Product-Specific Prerequisite Checks:

o Ignore the warning on physical memory requirement.

5. Specify Cluster Configuration: Click on Add.

o Public Node Name: tempracsrv4.mycorpdomain.com

o Private Node Name: tempracsrv4-priv.mycorpdomain.com

o Virtual Host Name: tempracsrv4-vip.mycorpdomain.com

6. Specify Network Interface Usage:

o Interface Name: eth0

o Subnet: 192.168.2.0

o Interface Type: Public

o Interface Name: eth1

o Subnet: 10.10.10.0

o Interface Type: Private

7. Specify Oracle Cluster Registry (OCR) Location: Select External Redundancy.

For simplicity, here you will not mirror the OCR. In a production environment,

you may want to consider multiplexing the OCR for higher redundancy.

o Specify OCR Location: /u01/ocr_config

8. Specify Voting Disk Location: Select External Redundancy.

Similarly, for simplicity, we have chosen not to mirror the Voting Disk.

o Voting Disk Location: /u01/votingdisk

9. Summary: Click on Install.

10. Execute Configuration scripts: Execute the scripts below as the root user

sequentially, one at a time. Do not proceed to the next script until the current

script completes.

o Execute /u01/app/oracle/oraInventory/orainstRoot.sh on tempracsrv3.

o Execute /u01/app/oracle/oraInventory/orainstRoot.sh on tempracsrv4.

o Execute /u01/app/oracle/product/10.2.0/crs_1/root.sh on tempracsrv3.

o Execute /u01/app/oracle/product/10.2.0/crs_1/root.sh on tempracsrv4.

The root.sh script on tempracsrv4 invoked the VIPCA automatically but it failed with the

error “The given interface(s), “eth0″ is not public. Public interfaces should be

used to configure virtual IPs.” As you are using a non-routable IP address

(192.168.x.x) for the public interface, the Oracle Cluster Verification Utility

(CVU) could not find a suitable public interface. A workaround is to run VIPCA

manually.

11. As the root user, manually invokes VIPCA on the second node.

# /u01/app/oracle/product/10.2.0/crs_1/bin/vipca

Welcome: Click on Next.

Network Interfaces: Select eth0.

Virtual IPs for cluster nodes:

o Node name: tempracsrv3

o IP Alias Name: tempracsrv3-vip

o IP address: 192.168.2.31

o Subnet Mask: 255.255.255.0

o Node name: tempracsrv4

o IP Alias Name: tempracsrv4-vip

o IP address: 192.168.2.32

o Subnet Mask: 255.255.255.0

Summary: Click on Finish.

Configuration Assistant Progress Dialog: After the configuration has completed,

Click on OK.

Configuration Results: Click on Exit.

Return to the Execute Configuration scripts screen on tempracsrv3 and click on OK.

Configuration Assistants: Verify that all checks are successful. The OUI does a

Clusterware post-installation check at the end. If the CVU fails, correct the

Problem and re-run the following command as the oracle user.

tempracsrv3-> /u01/app/oracle/product/10.2.0/crs_1/bin/cluvfy stage -post crsinst -n tempracsrv3, tempracsrv4

23. Performing post-checks for cluster services setup

24.

25. Checking node reachability…

26. Node reachability check passed from node “tempracsrv3”.

27.

28. Checking user equivalence…

29. User equivalence check passed for user “oracle”.

30.

31. Checking Cluster manager integrity…

32.

33. Checking CSS daemon…

34. Daemon status check passed for “CSS daemon”.

35.

36. Cluster manager integrity check passed.

37.

38. Checking cluster integrity…

39.

40. Cluster integrity check passed

41.

42. Checking OCR integrity…

43.

44. Checking the absence of a non-clustered configuration…

45. All nodes free of non-clustered, local-only configurations.

46.

47. Uniqueness check for OCR device passed.

48.

49. Checking the version of OCR…

50. OCR of correct Version “2” exists.

51.

52. Checking data integrity of OCR…

53. Data integrity check for OCR passed.

54.

55. OCR integrity check passed.

56.

57. Checking CRS integrity…

58.

59. Checking daemon liveness…

60. Liveness check passed for “CRS daemon”.

61.

62. Checking daemon liveness…

63. Liveness check passed for “CSS daemon”.

64.

65. Checking daemon liveness…

66. Liveness check passed for “EVM daemon”.

67.

68. Checking CRS health…

69. CRS health check passed.

70.

71. CRS integrity check passed.

72.

73. Checking node application existence…

74.

75. Checking existence of VIP node application (required)

76. Check passed.

77.

78. Checking existence of ONS node application (optional)

79. Check passed.

80.

81. Checking existence of GSD node application (optional)

82. Check passed.

83.

84. Post-check for cluster services setup was successful.

85. End of Installation: Click on Exit.

2. Install Oracle Database 10g Release 2

After mounting database 10g R2 DVD run the installer

tempracsrv3-> ./runInstaller

1. Welcome: Click on Next.

2. Select Installation Type:

o Select Enterprise Edition.

3. Specify Home Details:

o Name: OraDb10g_home1

o Path: /u01/app/oracle/product/10.2.0/db_1

4. Specify Hardware Cluster Installation Mode:

o

Select the “Cluster Install” option and make sure both RAC nodes are selected, the click the “Next” button

o Select the “Install database Software only” option, then click the “Next” button.

Wait while the prerequisite checks are done. If you have any failures correct them and retry the tests before clicking the “Next” button.

7. Select the “Install database Software only” option, then click the “Next” button.

8. On the “Summary” screen, click the “Install” button to continue.

9. Wait while the database software installs.

10. Once the installation is complete, wait while the configuration assistants run.

11. Execute the “root.sh” scripts on both nodes, as instructed on the “Execute Configuration scripts” screen, then click the “OK” button.

12. When the installation is complete, click the “Exit” button to leave the installer.

Create a Database using the DBCA

Login to tempracsrv3 as the oracle user and start the Database Configuration Assistant.

Run the command dbca

On the “Welcome” screen, select the “Oracle Real Application Clusters database” option and click the “Next” button.

Select the “Create a Database” option and click the “Next” button.

Highlight both RAC nodes(tempracsrv3,tempracsrv4) and click the “Next” button.

Select the “Custom Database” option and click the “Next” button.

Enter the values “DEVDB.WORLD” and “DEVDB” for the Global Database Name and SID Prefix respectively, and then click the “Next” button.

Accept the management options by clicking the “Next” button. If you are attempting the installation on a server with limited memory, you may prefer not to configure Enterprise Manager at this time.

Enter database passwords then click the “Next” button.

Select the “Cluster File System” option, then click the “Next” button.

Select the “Use Oracle-Managed Files” option and enter “/u01/oradata/” as the database location, then click the “Next” button.

Check the “Specify Flash Recovery Area” option and accept the default location by clicking the “Next” button.

(ORACLE_BASE)/flash_recovery_area

Uncheck all but the “Enterprise Manager Repository” option, then click the “Standard Database Components…” button.

Uncheck all but the “Oracle JVM” option, then click the “OK” button, followed by the “Next” button on the previous screen. If you are attempting the installation on a server with limited memory, you may prefer not to install the JVM at this time.

Accept the current database services configuration by clicking the “Next” button.

Select the “Custom” memory management option and accept the default settings by clicking the “Next” button.

Accept the database storage settings by clicking the “Next” button.

Accept the database creation options by clicking the “Finish” button.

Accept the summary information by clicking the “OK” button.

Wait while the database is created.

Once the database creation is complete you are presented with the A screen. Make a note of the information on the screen and click the “Exit” button.

VERIFYING STEPS:-

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 16 12:27:11 2006

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> CONN sys/password@rac1 AS SYSDBA

Connected.

SQL> SELECT instance_name, host_name FROM v$instance;

INSTANCE_NAME    HOST_NAME

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

devdb1             tempracsrv3

SQL> CONN sys/password@rac2 AS SYSDBA

Connected.

SQL> SELECT instance_name, host_name FROM v$instance;

INSTANCE_NAME    HOST_NAME

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

devdb2             tempracsrv4

Importing data

Login to the EM of the RAC

http://tempracsrv3:5500/em

Using sys as sysdba

And create the required tablespace as it was in the source…

Then import the entire dump file which we did via export in the oracle 9i database one by one with the same user in which schema the data was exported.

%imp

Objective : The two Solaris 10 prod nodes have Oracle 9i databases and I  want to export database to Oracle 10g X 4 node Solaris 10 RAC.

Exporting Database (from Oracle 9i Database):-

vi export.par
userid = “sys/change_on_install as sysdba” buffer 1MB FILESIZE=750MB file=dmpfil<1-20> FULL=Y DIRECT=Y LOG=exp_full.log
At later stage create identical tables paces with enough size in the target db and then import it.

Setting up 4 Solaris 10 nodes (tempracsrv3,tempracsr4) and (prodracsrv1,prodracsrv2) with NFS setup between them

For this Type of configuration you should have two Ethernet cards in each sever ,two switches for private interconnect(for networking the second eth. i.e. eth1).

Install Solaris 10 from ISO image or CD, Hit Enter to install in graphical mode.

Skip the media test and start the installation(IF ANY)
. Language Selection: <select your language preference>.
. Keyboard Configuration: <select your keyboard preference>.
. Installation Type: Custom.
. Disk Partitioning Setup: Automatically partition. .
Hostname –enter tempracsrv3

Same way install 4 nodes namely tempracsrv4, prodrac1,prodrac2

After installation give the ip address for eth0 and eth1 as follows

For tempracsrv3
192.168.2.131 tempracsrv3.mycorpdomain.com tempracsrv3 — for eth0
192.168.2.31 tempracsrv3-vip.mycorpdomain.com tempracsrv3-vip
10.10.10.31 tempracsrv3-priv.mycorpdomain.com tempracsrv3-priv —for eth1 ( leave gateway blank)

For tempracsrv4
192.168.2.132 tempracsrv4.mycorpdomain.com tempracsrv4
192.168.2.32 tempracsrv4-vip.mycorpdomain.com tempracsrv4-vip
10.10.10.32 tempracsrv4-priv.mycorpdomain.com tempracsrv4-priv – for eth1( leave gateway blank)

Connect the network wire from the second eth on each machine to the One Gigabit Switch)—Note this is separate switch

After that verify that both ip’s are pinging from both machines.

From tempracsrv3
Ping tempracsrv4
Ping tempracsrv4-priv
Ping tempracsrv3
Ping tempracsrv3-priv

From tempracsrv4
Ping tempracsrv3
Ping tempracsrv3-priv
ping tempracsrv4
Ping tempracsrv4-priv

Ensure that everything is pinging successfully. No need to worry about the vip.

After the Installation of Solaris 10 in tempracsrv3 . Make directory u01 under / Using the following command “mkdir /u01”

Create the oracle user.

As the root user, execute
Create group, —dba ,oinstall
# /usr/sbin/groupadd oinstall
# /usr/sbin/groupadd dba
# /usr/sbin/groupadd -g 200 oinstall
# /usr/sbin/groupadd -g 201 dba
# /usr/sbin/groupadd -g 202 oper

Test
# id -a oracle
Create user—oracle(oracle software owner)…

# /usr/sbin/useradd -u 200 -g oinstall -G dba[,oper] oracle
Verify that the user nobody exists in the system.

# /usr/sbin/useradd nobody
# passwd oracle

Preventing Oracle Clusterware Installation Errors Caused by stty Commands

During an Oracle Clusterware installation, Oracle Universal Installer uses SSH (if available) to run commands and copy files to the other nodes. During the installation, hidden files on the system (for example, .bashrc or .cshrc) will cause installation errors if they contain stty commands.

To avoid this problem, you must modify these files to suppress all output on STDERR, as in the following examples:

Configuring the oracle User’s Environment

Bourne, Bash, or Korn shell:
if [ -t 0 ]; then
stty intr ^C
fi

C shell:
test -t 0
if ($status == 0) then
stty intr ^C
endif

Configuring Kernel Parameters on Solaris 10

On Solaris 10 operating systems, verify that the kernel parameters shown in the following table are set to values greater than or equal to the recommended value shown. The table also contains the resource controls that replace the /etc/system file for a specific kernel parameter. The procedure following the table describes how to verify and set the values.

Configuring Kernel Parameters

Pre-Installation Tasks

On Solaris 10, use the following procedure to view the current value specified for resource controls, and to change them if necessary:

1. To view the current values of the resource control, enter the following commands:

# id -p // to verify the project id
uid=0(root) gid=0(root) projid=1 (user.root)
# prctl -n project.max-shm-memory -i project user.root
# prctl -n project.max-sem-ids -i project user.root

2. If you must change any of the current values, then:

To modify the value of max-shm-memory to 6 GB:
# prctl -n project.max-shm-memory -v 6442450944 -r -i project user.root
To modify the value of max-sem-ids to 256:
# prctl -n project.max-sem-ids -v 256 -r -i project user.root

Use the following procedure to modify the resource control project settings, so that they persist after a system restart:

Note: In Solaris 10, you are not required to make changes to the /etc/system file to implement the System V IPC. Solaris 10 uses the resource control facility for its implementation. However, Oracle recommends that you set both resource control and /etc/system/ parameters.

Operating system parameters not replaced by resource controls continue to affect performance and security on Solaris 10 systems. For further information, contact your Sun vendor. In case you have any problem just edit the /etc/system file.

Parameter Replaced by Resource Control : Recommended value

noexec_user_stack NA 1
semsys:seminfo_semmns project.max-sem-ids 100
semsys:seminfo_semmns NA 1024
semsys:seminfo_semmsl process.max-sem-nsems 256
semsys:seminfo_semvmx NA 32767
shmsys:shminfo_shmmax project.max-shm-memory 4294967295
shmsys:shminfo_shmmin NA 1
shmsys:shminfo_shmmni project.max-shm-ids 100
shmsys:shminfo_shmseg NA 10

Note: When you use the command prctl (Resource Control) to change system parameters, you do not need to restart the system for these parameter changes to take effect. However, the changed parameters do not persist after a system restart.

Checking UDP Parameter Settings

1. By default, Oracle instances are run as the oracle user of the dba group . A project with the name group.dba is created to serve as the default project for the oracle user. Run the command id to verify the default project for the oracle user:

# su – oracle
$ id -p
uid=100(oracle) gid=100(dba) projid=100(group.dba)
$ exit

2. To set the maximum shared memory size to 2 GB, run the projmod command:

# projmod -sK “project.max-shm-memory=(privileged,2G,deny)” group.dba
Alternatively, add the resource control value  project.max-shm-memory=(privileged,2147483648,deny) to the last field of the project entries for the Oracle project.

3. After these steps are complete, check the values for the /etc/project file using the following command:

# cat /etc/project
The output should be similar to the following:
system:0::::
user.root:1::::
noproject:2::::
default:3::::
group.staff:10::::
group.dba:100:Oracle default
project:::project.max-shmmemory=(privileged,2147483648,deny)

4. To verify that the resource control is active, check process ownership, and run the commands id and prctl, as in the following example:

# su – oracle
$ id -p
uid=100(oracle) gid=100(dba) projid=100(group.dba)

$ prctl -n project.max-shm-memory -i process $$
process: 5754: -bash
NAME PRIVILEGE VALUE FLAG ACTION RECIPIENT
project.max-shm-memory
privileged 2.00GB – deny

Checking UDP Parameter Settings

The User Data Protocol (UDP) parameter settings define the amount of send and receive buffer space for sending and receiving datagrams over an IP network. these settings affect cluster interconnect transmissions. If the buffers set by these parameters are too small, then incoming UDP datagrams can be dropped due to insufficient space, which requires send-side retransmission. This can result in poor cluster performance.

On Solaris, the UDP parameters are udp_recv_hiwat and udp_xmit_hiwat. OnSolaris 10 the default values for these parameters are 57344 bytes. Oracle recommends that you set these parameters to at least 65536 bytes.

Note: For additional information, refer to the Solaris Tunable Parameters Reference Manual.

Checking the Operating System Requirements Setup with CVU. To check current settings for udp_recv_hiwat and udp_xmit_hiwat, enter the following commands:

# ndd /dev/udp udp_xmit_hiwat
# ndd /dev/udp udp_recv_hiwat

To set the values of these parameters to 65536 bytes in current memory, enter the following commands:

# ndd -set /dev/udp udp_xmit_hiwat 65536
# ndd -set /dev/udp udp_recv_hiwat 65536

To set the values of these parameters to 65536 bytes on system restarts, open the /etc/system file, and enter the following lines:

set udp:xmit_hiwat=65536
set udp:udp_recv_hiwat=65536

Checking the Hardware and Operating System Setup with CVU

/dev/dvdrom/crs/Disk1/cluvfy/runcluvfy.sh stage –post hwos –n node1,node2

Checking NFS Buffer Size Parameters

then you must set the values for the NFS buffer size parameters rsize and wsize to at least 16384. Oracle recommends that you use the value 32768. For example, if you decide to use rsize and wsize buffer settings with the value 32768, then update the /etc/vfstab file on each node with an entry similar to the following:

nfs_server:/vol/DATA/oradata /home/oracle/netapp nfs -yes
rw,hard,nointr,rsize=32768,wsize=32768,tcp,noac,vers=3

If you use NFS mounts, then Oracle recommends that you use the option forcedirectio to force direct I/O for better performance. However, if you add forcedirectio to the mount option, then the same mount point cannot be used for Oracle software binaries, executables, shared libraries, and objects. You can only use the forcedirectio option for Oracle data files, the OCR, and voting disks.

For these mount points, enter the following line:

nfs_server:/vol/DATA/oradata /home/oracle/netapp nfs -yes
rw,hard,nointr,rsize=32768,wsize=32768,tcp,noac,forcedirectio,vers=3

Create the oracle user environment file.

/export/home/oracle/.profile
export PS1=”`/bin/hostname -s`-> ”
export EDITOR=vi
export ORACLE_SID=devdb1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crs_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/bin:
/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
umask 022

Create the filesystem directory structure. As the oracle user, execute

tempracsrv3-> mkdir -p $ORACLE_BASE/admin
tempracsrv3-> mkdir -p $ORACLE_HOME
tempracsrv3-> mkdir -p $ORA_CRS_HOME
tempracsrv3-> mkdir -p /u01/oradata/devdb

Increase the shell limits for the Oracle user. So that it has unlimited resources in the oracle user

Modify the /etc/hosts file.

# more /etc/hosts
127.0.0.1 localhost
192.168.2.131 tempracsrv3.mycorpdomain.com tempracsrv3
192.168.2.31 tempracsrv3-vip.mycorpdomain.com tempracsrv3-vip
10.10.10.31 tempracsrv3-priv.mycorpdomain.com tempracsrv3-priv
192.168.2.132 tempracsrv4.mycorpdomain.com tempracsrv4
192.168.2.32 tempracsrv4-vip.mycorpdomain.com tempracsrv4-vip
10.10.10.32 tempracsrv4-priv.mycorpdomain.com tempracsrv4-priv

Configuring SSH, Add in all the cluster.

$ ps -ef | grep sshd

Create RSA and DSA keys on each node: Complete the following steps on each node:

1. Log in as the oracle user.

2. If necessary, create the .ssh directory in the oracle user’s home directory and set the correct permissions on it:

$ mkdir ~/.ssh
$ chmod 700 ~/.ssh
$ chmod 700

3. Enter the following commands to generate an RSA key for version 2 of the SSH protocol:

$ /usr/bin/ssh-keygen -t rsa
At the prompts: Accept the default location for the key file.

Enter and confirm a pass phrase that is different from the oracle user’s password. This command writes the public key to the ~/.ssh/id_rsa.pub file and the  private key to the ~/.ssh/id_rsa file. Never distribute the private key to anyone.

4. Enter the following commands to generate a DSA key for version 2 of the SSH protocol:

$ /usr/bin/ssh-keygen -t dsa

At the prompts:

• Accept the default location for the key file
• Enter and confirm a pass phrase that is different from the oracle user’s password

This command writes the public key to the ~/.ssh/id_dsa.pub file and the private key to the ~/.ssh/id_dsa file. Never distribute the private key to anyone.

Add keys to an authorized key file: Complete the following steps:

1. On the local node, determine if you have an authorized key file (~/.ssh/authorized_keys). If the authorized key file already exists, then proceed to step 2. Otherwise, enter the following commands:

$ touch ~/.ssh/authorized_keys
$ cd ~/.ssh
$ ls

You should see the id_dsa.pub and id_rsa.pub keys that you have created.

2. Using SSH, copy the contents of the ~/.ssh/id_rsa.pub and ~/.ssh/id_dsa.pub files to the file ~/.ssh/authorized_keys, and provide the Oracle user password as prompted. This process is illustrated in the following syntax example with a two-node cluster, with nodes node1 and node2, where the Oracle user path is /home/oracle:

[oracle@node1 .ssh]$ ssh node1 cat /home/oracle/.ssh/id_rsa.pub >>
authorized_keys
oracle@node1’s password:
[oracle@node1 .ssh]$ ssh node1 cat /home/oracle/.ssh/id_dsa.pub >>
authorized_keys
[oracle@node1 .ssh$ ssh node2 cat /home/oracle/.ssh/id_rsa.pub >>
authorized_keys
oracle@node2’s password:
[oracle@node1 .ssh$ ssh node2 cat /home/oracle/.ssh/id_dsa.pub
>>authorized_keys
oracle@node2’s password:

3. Use SCP (Secure Copy) or SFTP (Secure FTP) to copy the authorized_keys file  to the Oracle user .ssh directory on a remote node. The following example is with SCP, on a node called node2, where the Oracle user path is /home/oracle:

[oracle@node1 .ssh]scp authorized_keys node2:/home/oracle/.ssh/

Note: Repeat this process for each node in the cluster.

Creating Required Operating System Groups and User

Pre-Installation Tasks

4. Repeat step 2 and 3 for each cluster node member. When you have added keys from each cluster node member to the authorized_keys file on the last node you want to have as a cluster node member, then use SCP to copy the complete authorized_keys file back to each cluster node member

5. Change the permissions on the Oracle user’s /.ssh/authorized_keys file on all cluster nodes:

$ chmod 600 ~/.ssh/authorized_keys

At this point, if you use ssh to log in to or run a command on another node, you are prompted for the pass phrase that you specified when you created the DSA key.

Enabling SSH User Equivalency on Cluster Member Nodes

To enable Oracle Universal Installer to use the ssh and scp commands without being prompted for a pass phrase, follow these steps:

1. On the system where you want to run Oracle Universal Installer, log in as the oracle user.

2. Enter the following commands:

$ exec /usr/bin/ssh-agent $SHELL
$ /usr/bin/ssh-add

3. At the prompts, enter the pass phrase for each key that you generated. If you have configured SSH correctly, then you can now use the ssh or scp commands without being prompted for a password or a pass phrase.

4. If you are on a remote terminal, and the local node has only one visual (which is typical), then use the following syntax to set the DISPLAY environment Variable:

Bourne, Korn, and Bash shells
$ export DISPLAY=hostname:0
C shell:
$ setenv DISPLAY 0
For example, if you are using the Bash shell, and if your hostname is node1, then enter the following command:
$ export DISPLAY=node1:0

5. To test the SSH configuration, enter the following commands from the same terminal session, testing the configuration of each cluster node, where tempracsrv3, tempracsrv4, and so on, are the names of nodes in the cluster:

$ ssh tempracsrv3 date
$ ssh tempracsrv4 date

Note: The Oracle user’s /.ssh/authorized_keys file on every node must contain the contents from all of the /.ssh/id_rsa.pub and /.ssh/id_dsa.pub files that you generated on all cluster nodes.

These commands should display the date set on each node. If any node prompts for a password or pass phrase, then verify that the ~/.ssh/authorized_keys file on that node contains the correct public keys. If you are using a remote client to connect to the local node, and you see a message similar to “Warning: No xauth data; using fake authentication data for X11 forwarding,” then this means that your authorized keys file is configured correctly, but your ssh configuration has X11 forwarding enabled. To correct this, proceed to step 6.

6. To ensure that X11 forwarding will not cause the installation to fail, create a user-level SSH client configuration file for the Oracle software owner user, as follows:

a. Using any text editor, edit or create the ~oracle/.ssh/config file.

b. Make sure that the ForwardX11 attribute is set to no. For example:

Host *
ForwardX11 no

Note: The first time you use SSH to connect to a node from a particular system, you may see a message similar to the following: The authenticity of host ‘node1 (140.87.152.153)’ can’t be established.

RSA key fingerprint is
7z:ez:e7:f6:f4:f2:4f:8f:9z:79:85:62:20:90:92:z9.
Are you sure you want to continue connecting (yes/no)?

Enter yes at the prompt to continue. You should not see this message again when you connect from this system to that node. If you see any other messages or text, apart from the date, then the installation can fail. Make any changes required to ensure that only the date is displayed when you enter these commands. You should ensure that any parts of login scripts that generate any output, or ask any questions, are modified so that they act only when the shell is an interactive shell.

At Ttempracsrv3

Create NFS mount point /u01. Create directory using following command Mkdir /u01

1. enable NFS sever by running the following:
svcadm -v enable -r network/nfs/server

3. Run the following command to share via NFS
share -F nfs -o rw /u01
Note: The above share command will not persist over reboots. To persist over reboots, add an entry to /etc/dfs/dfstab

At Tempracsrv4

4. Run the following command to mount from tempracsrv4: mount -F nfs tempracsrv3:/u01 /u01
Note: The above mount command will not persist over reboots. To persist over reboots, add the following line in /etc/vfstab:

tempracsrv3:/u01 – /u01 nfs – yes rw,soft

Now copy the first machine as we did for tempracsrv4 and configure it as temprac1,temprac2.(for failover).also configure /u02 as nfs in temprac1, and mount it in temprac2.

creating blank files which will be used later for placing voting disk and ocr

touch /u01/crs_config
touch /u01/voting_disk

Now configure prodrac1,prodrac2 as we did for tempracsrv3,tempracsrv4.(for failover).

Change the sid in .bash_profile from devdb to prod. Use the following ip’s for prodrac1,prodrac2

127.0.0.1 localhost
192.168.2.133 prodrac1.mycorpdomain.com prodrac1— eth0 on prodrac1
192.168.2.34 prodrac1-vip.mycorpdomain.com prodrac1-vip
10.10.10.33 prodrac1-priv.mycorpdomain.com prodrac1-priv –eth1 on prodrac1
192.168.2.133 prodrac2.mycorpdomain.com prodrac2—eth0 on prodrac2
192.168.2.34 prodrac2-vip.mycorpdomain.com prodrac2-vip
10.10.10.34 prodrac2-priv.mycorpdomain.com prodrac2-priv—eth1 on prodrac2

The scope of this STEP By STEP documentation will include the following workflow:

*First Phase*
The two prod nodes have Oracle 9i databases and we want to export the database to oracle 10g RAC. Step by Step export instructions for creating a backup copy of databases. Step By Step instruction on how to install new OS version of Sun Solaris 10 to replace Sun Solaris 9.

Setting Up nodes
Step by Step documentation for setting up 4 Sun Solaris 10 nodes. This portion needs to include Step by Step instructions for NFS setup. Also include step by step instructions to verify connectivity.

*Second Phase*
Oracle 10g R2 RAC Installation for Temp Nodes:
Step by Step instructions for installing Oracle 10g R2 RAC installation. The procedures will provide STEP by STEP guide you for installing two nodes (tempracsrv3 and tempracsrv4). Installation on this phase includes documentation on how to verify the installation and configuration is installed correctly. Step by Step instructions on creating RAC database, importing schemas from oracle 9i database into new databases, and testing database and node connectivity for this phase of the step by step instructions.

*Third Phase*
Oracle 10g R2 RAC Installation for PROD Nodes:
Step by Step instructions for installing Oracle 10g R2 RAC installation. The procedures will provide STEP By STEP guide you for installing two nodes (prodracsrv1and prodracsrv2) and adding to the existing RAC cluster.

*Fourth Phase*
Step by Step instructions on how to fail RAC databases over from temp nodes to prod nodes. Includes step by step instructions on how to verify the failover from temp nodes to prod nodes. Step by Step instructions on how to test RAC database connectivity after failover.

*Fifth Phase*
Step by Step instructions on how to remove temp nodes from RAC cluster. Step by step instruction on how to verify removal of temp nodes.

— Server A – Is a Live Server
— Server B – Is a Physical Standby Server
Both were configured for a data guard

One fine day a standby blows away and we need to recreate it from Live

At Live Server A

1. There is no need but if you want please disable Dataguard Broker

SQLPLUS> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQLPLUS> alter system set dg_broker_start=FALSE

DGMGRL> show configuration verbose;

Configuration
Name: odba
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
catp_hongkong – Primary database
catp_morocco – Physical standby database (disabled)

2. Put current database into a HOT backup mode.
Backup Datafiles, Controlfiles and Redo logs

Copy files from backup set on a standby database.

At Standby Server B

3. Please make sure following parameters are set on a Physical Standby database init.ora file

4. Recover Standby Database Now

SQL> connect / as sysdba
Connected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle1/oradata/catp
Oldest online log sequence 199166
Next log sequence to archive 199169
Current log sequence 199169
SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=TRUE scope=both;
System altered.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2030296 bytes
Variable Size 503317800 bytes
Database Buffers 1627389952 bytes
Redo Buffers 14745600 bytes

SQL> show parameter dg_broker_start

NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQL> recover managed standby database disconnect from session;
ORA-01507: database not mounted

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

Monitor Alrt log for messages for ARCHIVE Success

Media Recovery Waiting for thread 1 sequence 199169
Fetching gap sequence in thread 1, gap sequence 199169-199177
Tue Jun 5 15:54:45 2007
Redo Shipping Client Connected as PUBLIC
— Connected User is Valid
RFS[2]: Assigned to RFS process 3397
RFS[2]: Identified database type as ‘physical standby’
RFS[2]: No standby redo logfiles created
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199179_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199169_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199170_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199171_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199172_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199173_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199174_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199175_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199176_1_528884944.arc’
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199177_1_528884944.arc’
Tue Jun 5 15:55:15 2007
Media Recovery Log /oracle1/oradata/catp/catp_0000199169_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199170_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199171_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199172_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199173_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199174_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199175_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199176_1_528884944.arc
Media Recovery Log /oracle1/oradata/catp/catp_0000199177_1_528884944.arc
Media Recovery Waiting for thread 1 sequence 199178
Fetching gap sequence in thread 1, gap sequence 199178-199178
Tue Jun 5 15:55:18 2007
RFS[2]: Archived Log: ‘/oracle1/oradata/catp/catp_0000199178_1_528884944.arc’

You can check v$archived_log and v$dataguard_status views to find out logs applied and errors if any respectively.

1. Logs Applied on Standby
Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;
OR
select sequence#, applied ,completion_time from v$archived_log where STANDBY_DEST=’YES’ order by 3 desc

2. Error message at Dataguard
Select to_char(TIMESTAMP, ‘dd-mon-yyyy hh24:mm:ss’), MESSAGE from v$dataguard_status;

Sometimes Oracle throws error messages at ALRTLOG – Failed to request gap sequence

Reason : DG FAL failed to copy archive files from PRIMARY to STANDBY

Media Recovery Waiting for thread 1 sequence 84730
Fetching gap sequence in thread 1, gap sequence 84730-85042
FAL[client]: Trying FAL server: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=zagreb)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=bfp_zagreb_XPT.yell)(SERVER=dedicated)))
Mon Nov 12 15:06:45 2007
Completed: ALTER DATABASE RECOVER managed standby database d
Mon Nov 12 15:11:45 2007
FAL[client]: Failed to request gap sequence for thread 1 gap sequence 84730-85042
FAL[client]: All defined FAL servers have been attempted.

Fix:

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> Select sequence#, applied from v$archived_log where STANDBY_DEST=’YES’ order by sequence#;

no rows selected

SQL> recover standby database;
ORA-01153: an incompatible media recovery is active

STOP EARLIER recovery session

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

SQL> recover standby database;
ORA-00279: change 1967405177515 generated at 11/08/2007 10:06:55 needed for
thread 1
ORA-00289: suggestion : /oracle1/oradata/bfp/bfp_1_569156389_0000084730.arc
ORA-00280: change 1967405177515 for thread 1 is in sequence #84730

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto

This will end up as

ORA-00308: cannot open archived log
‘/oracle1/oradata/bfp/bfp_1_569156389_0000084800.arc’
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Rman Script to Create Standby From Live Server
connect target rman/obst@live
connect auxiliary sys/ams@standby
connect rcvcat rmanc/obss@rcvcat
Run {
allocate auxiliary channel tp1 type “sbt_tape”;
allocate auxiliary channel tp2 type “sbt_tape”;
send ‘NSR_ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default,NSR_CLIENT=miles)’;
duplicate target database for standby dorecover nofilenamecheck;
release channel tp1;
release channel tp2;
}

Above script will return an error
ORA-01830: date format picture ends before converting entire input string
Add a line
Run {
set until time “to_date(‘Dec 11 2007 06:30:09′,’Mon DD YYYY HH24:MI:SS’)”;

Restore Archivelogs which were backed up thru rman

connect rcvcat rmanc/obseq@rcvcat
connect target rmant/obus@live
run {
allocate channel tp1 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
allocate channel tp2 type “sbt_tape”
parms ‘ENV=(NSR_SERVER=bsbk10,NSR_DATA_VOLUME_POOL=Default)’;
set archivelog destination to ‘f:\oracle\oradata\live’;
restore archivelog from logseq 111094 until logseq 111111;
release channel tp1;
release channel tp2;

Run RMAN script to create log as
rman cmdfile=create_standby.rman msglog=create_standby.log

• Select database for catalogue
• Create catalogue owner RMANC, default tablespace TOOLS
• Grant RECOVERY_CATALOG_OWNER role to RMANC
• Run the RMAN executable, being the same version as the catalogue database codeset and connect to the catalogue as the RMANC user
• Issue the CREATE CATALOG command

At RMAN repository Database

SQL>   create user rmanc identified by xxxxxxxx Temporary tablespace TEMP Default tablespace TOOLS quota unlimited on TOOLS;
SQL>   grant RECOVERY_CATALOG_OWNER to RMANC;
%RMAN catalog RMANC/xxxxxxxx@catdb
rman>   CREATE CATALOG

At Target Database which need to be backed up thru RMAN. Before registering a target database, create the target rman user in the target database.

SQL>   connect / as sysdba
SQL>   create user RMANT identified by xxxxxxxx
Default tablespace TOOLS
Temporary tablespace TEMP;
SQL>   grant SYSDBA,CREATE SESSION to RMANT;

Initialisation Parameters : To improve backup performance following parameters must be set….

BACKUP_TAPE_IO_SLAVES = TRUE
For 8i
LARGE_POOL_SIZE = (4 *
<number> *
DB_BLOCK_SIZE *
DB_FILE_DIRECT_IO_COUNT *
<level>
) +
(4 *
<number> *
<size>
)

For 9i
LARGE_POOL_SIZE= <number>*
(16MB +
<size>
)

The size of a tape buffer is determined by the RMAN parameter BLKSIZE.
BLKSIZE is OS dependant, but performance benefits can be made by explicitly setting it higher. 4M seems to offer improved performance. When testing a new backup, try differing values, in conjunction with the above calculation for LARGE_POOL_SIZE, to determine the optimal setting.

Net Requirements
RMAN requires a dedicated server connection to the target database.

Targetdb_rman.domain =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=dbname.db_domain)(SERVER=DEDICATED))
)

The above should be configured on both the target and catalogue servers. The server hosting the target database also requires a Net configuration for the catalogue database, as this is where the RMAN commands are normally issued.

Database registration: To register the target database, issue the following commands.

rman TARGET  rmanc/xxxxxxxx@targetdb  CATALOG rmant/xxxxxxxx@cataloguedb
rman> REGISTER DATABASE;
To check successful registration, issue the following command whilst still in RMAN;
rman> REPORT SCHEMA;
Report of database schema
File K-bytes    Tablespace           RB segs Datafile Name
—- ———- ——————– ——- ——————-
1        524288 SYSTEM               YES     G:\ORACLE\ORADATA\TKNWP\SYSTEM01.DB
F
2        524288 UNDOTBS              YES     G:\ORACLE\ORADATA\TKNWP\UNDOTBS01.D
BF
3         20480 GENESYS_CONFIG_DATA  NO      I:\ORACLE\ORADATA\TKNWP\GENESYS_CON
FIG_DATA_01.DBF
4         20480 GENESYS_LOGS_DATA    NO      G:\ORACLE\ORADATA\TKNWP\GENESYS_LOG
S_DATA_01.DBF
5        131072 TOOLS                NO      H:\ORACLE\ORADATA\TKNWP\TOOLS_01.DB
F
6         25600 USERS                NO      H:\ORACLE\ORADATA\TKNWP\USERS_01.DB
F
7        256000 PERFSTAT             NO      G:\ORACLE\ORADATA\TKNWP\PERFSTAT_01
.DBF

Displays partition information for the specified index, or all indexes.

SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFFSELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM   dba_ind_partitions a
WHERE  a.index_name  = Decode(‘BRANCH_STATS_IX’,’ALL’,a.index_name,’BRANCH_STATS_IX’)
ORDER BY a.index_name, a.partition_name;

Replace ‘BRANCH_STATS_IX’ with valid index_name

select ‘Alter Index ‘|| index_owner ||’.’||index_name
||’ Rebuild Partition ‘ || partition_name ||’  Online;
‘ from dba_ind_partitions a
WHERE  a.index_name  = Decode(‘BRANCH_STATS_IX’,’ALL’,a.index_name,’BRANCH_STATS_IX’)
ORDER BY a.index_name, a.partition_name

This should create script like …
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060201 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060202 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060203 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060204 Online;

Problem : Oracle reported block corruption on data file id 21,22 mapped for ABC,XYZ tablespaces.

Get list of all partitions mapped into corrupt tablespaces

SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM dba_ind_partitions a
WHERE a.tablespace_name in (‘ABC’,’XYZ’)
ORDER BY a.index_name, a.partition_name

Get a script to rebuild all these partitions

SELECT ‘Alter Index ‘|| index_owner ||’.’||index_name ||’ Rebuild Partition ‘ || partition_name ||’ Online; ‘
FROM dba_ind_partitions a
WHERE a.tablespace_name in (‘ABC,’XYZ)
ORDER BY a.index_name, a.partition_name;

undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level

Actual Undo Size

SELECT   SUM (a.bytes) "UNDO_SIZE"
 FROM   v$datafile a, v$tablespace b, dba_tablespaces c
 WHERE       c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#;

UNDO_SIZE
———-
209715200

Undo Blocks per Second

SELECT   MAX (undoblks / ( (end_time - begin_time) * 3600 * 24))
 "UNDO_BLOCK_PER_SEC"
 FROM   v$undostat;

UNDO_BLOCK_PER_SEC
——————
3.12166667

DB Block Size

SELECT   TO_NUMBER (VALUE) "DB_BLOCK_SIZE [KByte]"
 FROM   v$parameter
 WHERE   name = 'db_block_size';

DB_BLOCK_SIZE [Byte]
——————–
4096

Optimal Undo Retention

209’715’200 / (3.12166667 * 4’096) = 16’401 [Sec]

Using Inline Views, you can do all in one query!

SELECT   d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION [Sec]",
 ROUND (
 (d.undo_size / (TO_NUMBER (f.VALUE) * g.undo_block_per_sec))
 )
 "OPTIMAL UNDO RETENTION [Sec]"
 FROM   (SELECT   SUM (a.bytes) undo_size
 FROM   v$datafile a, v$tablespace b, dba_tablespaces c
 WHERE       c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#) d,
 v$parameter e,
 v$parameter f,
 (SELECT   MAX (undoblks / ( (end_time - begin_time) * 3600 * 24))
 undo_block_per_sec
 FROM   v$undostat) g
 WHERE   e.name = 'undo_retention' AND f.name = 'db_block_size';

ACTUAL UNDO SIZE [MByte]
————————
200

UNDO RETENTION [Sec]
——————–
10800

OPTIMAL UNDO RETENTION [Sec]
—————————-
16401

Calculate Needed UNDO Size for given Database Activity

SELECT   d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION [Sec]",
 (TO_NUMBER (e.VALUE) * TO_NUMBER (f.VALUE) * g.undo_block_per_sec)
 / (1024 * 1024)
 "NEEDED UNDO SIZE [MByte]"
 FROM   (SELECT   SUM (a.bytes) undo_size
 FROM   v$datafile a, v$tablespace b, dba_tablespaces c
 WHERE       c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#) d,
 v$parameter e,
 v$parameter f,
 (SELECT   MAX (undoblks / ( (end_time - begin_time) * 3600 * 24))
 undo_block_per_sec
 FROM   v$undostat) g
 WHERE   e.name = 'undo_retention' AND f.name = 'db_block_size';

ACTUAL UNDO SIZE [MByte]
————————
200
UNDO RETENTION [Sec]
——————–
10800
NEEDED UNDO SIZE [MByte]
————————
131.695313

It’s a tricky question. I have explained here by using a 33GB SAP BW driving table called VBAP which needed a major work. At end I had to rebuild this table using Quest Shareplex.

VBAP Table acquired 3,873,549 blocks = 30.2 GB in Space , The total number of rows were 15,900,000
So Rows per block = 15,900,000/3,873,549 blocks= 4 rows per block

The oracle analyze stats show Avg row length of 1256 bytes so for a 8KB block size

Ideal block count would be = 15,900,000/6 rows per block(avg row len 1256 bytes) = 2,650,000 Blocks  not 3,873,549 blocks
Oracle currently using 45% more space due to row chaining.

I have used SQL script below to locate number of blocks for a 16 million row table. The row dist count below indicate on avg we have chained rows going down upto 3 database blocks.

select rows_per_block,

count(*) blocks,
sum(rows_per_block) sum_rows
from
(
select dbms_rowid.rowid_block_number(rowid),
count(*) rows_per_block
from SAPR3.vbap
group by dbms_rowid.rowid_block_number(rowid)
)
group by rows_per_block
order by 1 desc;

select to_char(floor(rows_per_block/10)*10,’fm990′)||”’s’ rows_per_block,
count(*) blocks,
sum(rows_per_block) sum_rows
from
(
select dbms_rowid.rowid_block_number(rowid),
count(*) rows_per_block
from t1
group by dbms_rowid.rowid_block_number(rowid)
)
group by to_char(floor(rows_per_block/10)*10,’fm990′)||”’s’
order by 1 desc;

ROWS_PER_BLOCK BLOCKS SUM_ROWS
53         1     53
51         4     204
50         3     150
49         6     294
48         5     240
47         19     893
46         35     1610
45         52     2340
44         77     3388
43         143     6149
42         181     7602
41         272     11152
40         387     15480
39         606     23634
38         814     30932
37         1119     41403
36         1475     53100
35         1985     69475
34         2627     89318
33         3472     114576
32         4262     136384
31         5299     164269
30         6662     199860
29         8107     235103

Why 1… 50 rows allocated in a single block?

It must be due to bespoke development done on table. I feel when table was originally populated , it had very less data & SAP-Oracle put those many rows into a single block but over period of time things have changed and new columns for bespoke development and data pushed row chaining on table. In short I can see a need for rebuilding this table.

% of Rows retrieved thru Row Chaining
http://www.akadia.com/services/ora_chained_rows.html

SELECT name,value FROM v$sysstat WHERE name like ‘%table%fetch%ro%’;

NAME VALUE
——————————————————————————-
table fetch by rowid 68617756592
table fetch continued row 4330753

Table fetch by rowid : Number of ROWS retrieved thru Direct ROWID
table fetch continued row : Number of ROWS going thru ROW CHAINING