Saturday, 30 July 2016

Important srvctl and crsctl commands

Displays the configuration for the SCAN VIP.

[root@rac2 Desktop]# srvctl config scan -i 1
SCAN name: rac-scan, Network: 1/192.192.2.0/255.255.255.0/eth4
SCAN VIP name: scan1, IP: /rac-scan/192.192.2.51


Displays the configuration information for a VIP or VIP's configured on a node.

[root@rac2 Desktop]# srvctl config vip  -n rac2
VIP exists: /rac2-vip/192.192.2.202/192.192.2.0/255.255.255.0/eth4, hosting node rac2

Displays configuration information for the SCAN listener.

[root@rac2 Desktop]# srvctl config scan_listener -i 1
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

[root@rac2 Desktop]# srvctl config nodeapps
Network exists: 1/192.192.2.0/255.255.255.0/eth4, type static
VIP exists: /rac1-vip/192.192.2.201/192.192.2.0/255.255.255.0/eth4, hosting node rac1
VIP exists: /rac2-vip/192.192.2.202/192.192.2.0/255.255.255.0/eth4, hosting node rac2
GSD exists
ONS exists: Local port 6100, remote port 6200, EM port 2016

Temporarily relocates service from one node of the cluster to another.
 mandatory options are either -i, -t or -c, -n, but not both.

[root@rac2 Desktop]# srvctl relocate service -d orcl -s taf_service  -i orcl1 -t orcl2, orcl3


Display OHAS autostart config on this server
[root@rac2 Desktop]# crsctl disable crs
     Disable OHAS autostart on this server

  crsctl query crs activeversion
     Lists the Oracle Clusterware operating version
  crsctl query crs releaseversion
     Lists the Oracle Clusterware release version
  crsctl query crs softwareversion [<nodename>| -all]
     Lists the version of Oracle Clusterware software installed

Find public and private network ip addresses

[root@rac2 Desktop]# oifcfg getif
eth5  10.10.2.0  global  cluster_interconnect
eth4  192.192.2.0  global  public

check $CRS_HOME/cdata/<cluster_name> directory 

Friday, 29 July 2016

Dba_jobs view

DESC DBA_JOBS
----------------------
JOB
SEHEMA_USER
LAST_DATE
THIS_DATE
NEXT_DATE
WHAT     --> Body of the anonymous PL/SQL block that the job executes
 

Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.
As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session
Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.
ALTER SYSTEM KILL SESSION 'sid,serial#';
4. Kill the O/S Process
More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill '9 spid
The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running
Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.
8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.
9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.
SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):
10. Alter the Job Queue to Original Value
Set the job queue to its' original value so that the jobs can run again.
ALTER SYSTEM SET job_queue_processes = original_value;
11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.
 
 
Check
dba_seheduler_jobs
dba_seheduler_jobs_running
DBA_jobs
DBA_jobs_running
v$session_longops

 
You need to identify the session of the job in v$session.
In v$session there is much more information.
Amongst others the sql_id of the statement it is executing
V$sql_plan will show the plan of that sql.
V$session_wait will show what it is waiting for
v$sess_io will show whether any IO is going on
v$session_longops will show what long operations are going on and how far they are.
 
 
1-the alert_log for this instance;
2- select * from dba_objects;
3- either windows or unix check what other jobs are schuelded;
4- you will need to use stats pack for thissituation.
so take astatspack before and while running this job.
5- show your sp code.
6- an Xplain plan for the select statement.
7-invoke the job and check locks with

Thursday, 21 July 2016

Adding a Service to cluster using SRVCTL

Services in Oracle Database 10g::

In Real Application Cluster (RAC) environments it is sometimes desirable to run applications on a subset of RAC nodes, or have preferred nodes for specific applications. In Oracle 10g this is accomplished using services.
•Cluster Configuration
•Service Creation
•Jobs and Services
•Connections and Services

Cluster Configuration :
Before using services, you must check the cluster configuration is correct. The following command and output show the expected configuration for a three node database called ORCL.

# srvctl config database -d ORCL
server01 ORCL1 /u01/app/oracle/product/10.1.0/db_1
server02 ORCL2 /u01/app/oracle/product/10.1.0/db_1
server03 ORCL3 /u01/app/oracle/product/10.1.0/db_1

Service Creation :
The srvctl utility, dbca utility and DBMS_SERVICES package can all be used to create and modify services, but for this article we will restrict ourselves to looking at the srvctl utility. Let's assume we have two applications that should run in the following way.
•OLTP - Should run on nodes 1 and 2 of the RAC, but is able to run on node 3 if nodes 1 and 2 are not available.
•BATCH - Should run on node 3, but is able to run on nodes 1 and 2 if node 3 is not available.

To meet this requirement we might create the following services.
Set environment :
# export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
# export PATH=$ORACLE_HOME/bin:$PATH

Create services :
# srvctl add service -d ORCL -s OLTP_SERVICE -r ORCL1,ORCL2 -a ORCL3
# srvctl add service -d ORCL -s BATCH_SERVICE -r ORCL3 -a ORCL1,ORCL2

The OLTP_SERVICE is able to run on all RAC nodes because ORCL3 is present in the available list, but will run in preference on nodes 1 and 2 (indicated by the -r option).

The BATCH_SERVICE is able to run on all RAC nodes because ORCL1 and ORCL2 are in the available list, but will run in preference on node 3 (indicated by the -r option).

The services can be started and stopped using the following commands.
# srvctl start service -d ORCL -s OLTP_SERVICE
# srvctl start service -d ORCL -s BATCH_SERVICE

Jobs and Services

The Oracle 10g scheduler allows jobs to be linked with job classes, which in turn can be linked to services to allows jobs to run on specific nodes in a RAC environment. To support our requirements we might create two job classes as follows.

-- Create OLTP and BATCH job classes.
BEGIN
  DBMS_SCHEDULER.create_job_class(
    job_class_name => 'OLTP_JOB_CLASS',
    service        => 'OLTP_SERVICE');
  DBMS_SCHEDULER.create_job_class(
    job_class_name => 'BATCH_JOB_CLASS',
    service        => 'BATCH_SERVICE');
END;
/
-- Make sure the relevant users have access to the job classes.
GRANT EXECUTE ON sys.oltp_job_class TO my_user;
GRANT EXECUTE ON sys.batch_job_class TO my_user;
These job classes can then be assigned to existing jobs or during job creation.
-- Create a job associated with a job class.
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'my_user.oltp_job_test',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;',
    job_class       => 'SYS.OLTP_JOB_CLASS',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job linked to the OLTP_JOB_CLASS.');
END;
/
-- Assign a job class to an existing job.
EXEC DBMS_SCHEDULER.set_attribute('MY_BATCH_JOB', 'JOB_CLASS', 'BATCH_JOB_CLASS');

Connections and Services :

The use of services is not restricted to scheduled jobs. These services can be used in the tnsnames.ora file to influence which nodes are used for each applications. An example of the tnsnames.ora file entries are displayed below.

OLTP =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = OLTP_SERVICE)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 20)
        (DELAY = 1)
      )
    )
  )

BATCH =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server02)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = server03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = BATCH_SERVICE)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 20)
        (DELAY = 1)
      )
    )
  )

Provided applications use the appropriate connection identifier they should only connect to the nodes associated to the service.

Try:

$ sqlplus  system@BATCH_SERVICE

$ sqlplus system@OLTP_SERVICE

Monday, 18 July 2016

crsctl stat res -t and crs_stat -t Results


[root@rac1 ~]# crsctl stat res -t
--------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
---------------------------------------------------------------------
Local Resources
---------------------------------------------------------------------
ora.BACKUP.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.DATA.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.VOTE.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.asm
               ONLINE  ONLINE       rac1       Started
               ONLINE  ONLINE       rac2       Started
ora.gsd
               OFFLINE OFFLINE      rac1
               OFFLINE OFFLINE      rac2
ora.net1.network
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.ons
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
-----------------------------------------------------------------------
Cluster Resources
----------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1
ora.cvu
      1        ONLINE  ONLINE       rac1
ora.oc4j
      1        ONLINE  ONLINE       rac1
ora.orcl.db
      1        OFFLINE OFFLINE
      2        ONLINE  ONLINE       rac1        Open
ora.rac1.vip
      1        ONLINE  ONLINE       rac1
ora.rac2.vip
      1        ONLINE  ONLINE       rac2
ora.scan1.vip
      1        ONLINE  ONLINE       rac2
ora.scan2.vip
      1        ONLINE  ONLINE       rac1
ora.scan3.vip
      1        ONLINE  ONLINE       rac1
[root@rac1 ~]#



[root@rac2 ~]# crsctl stat res -t
---------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
---------------------------------------------------------------------
Local Resources
---------------------------------------------------------------------
ora.BACKUP.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.DATA.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.VOTE.dg
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.asm
               ONLINE  ONLINE       rac1       Started
               ONLINE  ONLINE       rac2       Started
ora.gsd
               OFFLINE OFFLINE      rac1
               OFFLINE OFFLINE      rac2
ora.net1.network
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
ora.ons
               ONLINE  ONLINE       rac1
               ONLINE  ONLINE       rac2
---------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac2
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1
ora.cvu
      1        ONLINE  ONLINE       rac1
ora.oc4j
      1        ONLINE  ONLINE       rac1
ora.orcl.db
      1        OFFLINE OFFLINE
      2        ONLINE  ONLINE       rac1        Open
ora.rac1.vip
      1        ONLINE  ONLINE       rac1
ora.rac2.vip
      1        ONLINE  ONLINE       rac2
ora.scan1.vip
      1        ONLINE  ONLINE       rac2
ora.scan2.vip
      1        ONLINE  ONLINE       rac1
ora.scan3.vip
      1        ONLINE  ONLINE       rac1
[root@rac2 ~]#


[root@rac1 ~]# crsctl stat res -t
----------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
----------------------------------------------------------------------
Local Resources
----------------------------------------------------------------------
ora.BACKUP.dg
               ONLINE  ONLINE       rac1
ora.DATA.dg
               ONLINE  ONLINE       rac1
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1
ora.VOTE.dg
               ONLINE  ONLINE       rac1
ora.asm
               ONLINE  ONLINE       rac1      Started
ora.gsd
               OFFLINE OFFLINE      rac1
ora.net1.network
               ONLINE  ONLINE       rac1
ora.ons
               ONLINE  ONLINE       rac1
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac1
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       rac1
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       rac1
ora.cvu
      1        ONLINE  ONLINE       rac1
ora.oc4j
      1        ONLINE  ONLINE       rac1
ora.orcl.db
      1        OFFLINE OFFLINE
      2        ONLINE  ONLINE       rac1         Open
ora.rac1.vip
      1        ONLINE  ONLINE       rac1
ora.rac2.vip
      1        ONLINE  INTERMEDIATE rac1                                                                   FAILED OVER
ora.scan1.vip
      1        ONLINE  ONLINE       rac1
ora.scan2.vip
      1        ONLINE  ONLINE       rac1
ora.scan3.vip
      1        ONLINE  ONLINE       rac1
[root@rac1 ~]#



[root@rac1 ~]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
[root@rac1 ~]# crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.BACKUP.dg  ora....up.type ONLINE    ONLINE    rac1
ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N2.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N3.lsnr ora....er.type ONLINE    ONLINE    rac1
ora.VOTE.dg    ora....up.type ONLINE    ONLINE    rac1
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    rac1
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1
ora.orcl.db    ora....se.type ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    OFFLINE   OFFLINE
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac1
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    rac1
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    rac1
[root@rac1 ~]#
[root@rac1 ~]# crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.BACKUP.dg  ora....up.type ONLINE    ONLINE    rac1
ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N1.lsnr ora....er.type ONLINE    OFFLINE
ora....N2.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N3.lsnr ora....er.type ONLINE    ONLINE    rac1
ora.VOTE.dg    ora....up.type ONLINE    ONLINE    rac1
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    rac1
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1
ora.orcl.db    ora....se.type ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    OFFLINE   OFFLINE
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    OFFLINE
ora.rac2.gsd   application    OFFLINE   OFFLINE
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   ora....t1.type ONLINE    OFFLINE
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    rac1
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    rac1
[root@rac1 ~]#



[root@rac2 ~]# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4534: Cannot communicate with Event Manager
[root@rac2 ~]# crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.BACKUP.dg  ora....up.type ONLINE    ONLINE    rac1
ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N2.lsnr ora....er.type ONLINE    ONLINE    rac1
ora....N3.lsnr ora....er.type ONLINE    ONLINE    rac1
ora.VOTE.dg    ora....up.type ONLINE    ONLINE    rac1
ora.asm        ora.asm.type   ONLINE    ONLINE    rac1
ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac1
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    rac1
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac1
ora.ons        ora.ons.type   ONLINE    ONLINE    rac1
ora.orcl.db    ora....se.type ONLINE    ONLINE    rac1
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    OFFLINE   OFFLINE
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    OFFLINE
ora.rac2.gsd   application    OFFLINE   OFFLINE
ora.rac2.ons   application    ONLINE    OFFLINE
ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac1
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    rac1
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    rac1
[root@rac2 ~]#

Sunday, 17 July 2016

Clusterware startup process:

Clusterware Stratup :
- When a node of an Oracle Clusterware cluster restarts, OHASD is started by platform-specific means. OHASD accesses OLR (Oracle Local Registry) stored on the local file system to get the data needed to complete OHASD initialization
-  OHASD brings up GPNPD and CSSD. CSSD accesses the GPNP Profile stored on the local file system which contains the following vital bootstrap data;
a.  ASM_DISKSTRING parameter (if specified) to locate the disks on which ASM disks are configured.
b.  ASM SPFILE location : Name of the diskgroup containing ASM spfile
c.  Voting Disk Files location : ASM
– CSSD scans the headers of all ASM disks ( as indicated in ASM_DISKSTRING in GPnP profile) to identify the disk containing the voting file.  Using the pointers in ASM disk headers, the Voting Files locations on ASM Disks are accessed by CSSD and CSSD is able to complete initialization and start or join an existing cluster.
–To read the ASM spfile during the ASM instance startup, it is not necessary to open the disk group. All information necessary to access the data is stored in the device’s header. OHASD reads the header of the ASM disk containing ASM SPfile (as read from GPnP profile) and using the pointers in disk header, contents of ASM spfile are read. Thereafter, ASM instance is started.
–  With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRSD.
–  OHASD starts CRSD with access to the OCR in an ASM Diskgroup.
–  Clusterware completes initialization and brings up other services under its control.
Demonstration :
In my environment, the ASM disk group DATA created with EXTERNAL  redundancy is used exclusively for ASM spfile, voting and OCR files:
- Let us read  gpnp profile to find out the location of ASM SPfile
[grid@host01 peer]$ cd /u01/app/11.2.0/grid/gpnp/host01/profiles/peer
                                  gpnptool getpval -asm_spf
Warning: some command line parameters were defaulted. Resulting command line:
         /u01/app/11.2.0/grid/bin/gpnptool.bin getpval -asm_spf -p=profile.xml -o-
+DATA/cluster01/asmparameterfile/registry.253.793721441
– Let us find out the disks in DATA diskgroup
[grid@host01 peer]$ asmcmd lsdsk -G DATA
Path
ORCL:ASMDISK01
ORCL:ASMDISK010
ORCL:ASMDISK02
ORCL:ASMDISK03
ORCL:ASMDISK04
ORCL:ASMDISK09
– Let us find out which ASM disk maps to which partition
   Note down major/minor device numbers of the disks in DATA diskgroup
[root@host01 ~]# ls -lr /dev/oracleasm/disks/*
brw-rw—- 1 grid asmadmin 8, 26 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK09
brw-rw—- 1 grid asmadmin 8, 25 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK08
brw-rw—- 1 grid asmadmin 8, 24 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK07
brw-rw—- 1 grid asmadmin 8, 23 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK06
brw-rw—- 1 grid asmadmin 8, 22 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK05
brw-rw—- 1 grid asmadmin 8, 21 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK04
brw-rw—- 1 grid asmadmin 8, 19 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK03
brw-rw—- 1 grid asmadmin 8, 18 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK02
brw-rw—- 1 grid asmadmin 8, 31 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK014
brw-rw—- 1 grid asmadmin 8, 30 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK013
brw-rw—- 1 grid asmadmin 8, 29 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK012
brw-rw—- 1 grid asmadmin 8, 28 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK011
brw-rw—- 1 grid asmadmin 8, 27 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK010
brw-rw—- 1 grid asmadmin 8, 17 Nov  8 09:35 /dev/oracleasm/disks/ASMDISK01
- Let us find out the major/minor device numbers of various disk partitions
[root@host01 ~]# ls -lr /dev/sdb*
brw-r—– 1 root disk 8, 25 Nov  8 09:35 /dev/sdb9
brw-r—– 1 root disk 8, 24 Nov  8 09:35 /dev/sdb8
brw-r—– 1 root disk 8, 23 Nov  8 09:35 /dev/sdb7
brw-r—– 1 root disk 8, 22 Nov  8 09:35 /dev/sdb6
brw-r—– 1 root disk 8, 21 Nov  8 09:35 /dev/sdb5
brw-r—– 1 root disk 8, 20 Nov  8 09:35 /dev/sdb4
brw-r—– 1 root disk 8, 19 Nov  8 09:35 /dev/sdb3
brw-r—– 1 root disk 8, 18 Nov  8 09:35 /dev/sdb2
brw-r—– 1 root disk 8, 31 Nov  8 09:35 /dev/sdb15
brw-r—– 1 root disk 8, 30 Nov  8 09:35 /dev/sdb14
brw-r—– 1 root disk 8, 29 Nov  8 09:35 /dev/sdb13
brw-r—– 1 root disk 8, 28 Nov  8 09:35 /dev/sdb12
brw-r—– 1 root disk 8, 27 Nov  8 09:35 /dev/sdb11
brw-r—– 1 root disk 8, 26 Nov  8 09:35 /dev/sdb10
brw-r—– 1 root disk 8, 17 Nov  8 09:35 /dev/sdb1
brw-r—– 1 root disk 8, 16 Nov  8 09:35 /dev/sdb
– Now we can find out the partitions mapping to various ASM disks by matching their
   major/minor device numbers
 ASMDISK01    8,17     /dev/sdb1
 ASMDISK02    8,18     /dev/sdb2
 ASMDISK03    8,19     /dev/sdb3
 ASMDISK04    8,21     /dev/sdb5
 ASMDISK09    8,26     /dev/sdb10
 ASMDISK10    8,27     /dev/sdb11
– Let’s scan the headers of those devices:
[root@host01 ~]#  kfed read /dev/sdb1 | grep -E ‘spf|ausize’
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
[root@host01 ~]#  kfed read /dev/sdb2 | grep -E ‘spf|ausize’
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000
kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000
[root@host01 ~]#  kfed read /dev/sdb3 | grep -E ‘spf|ausize’
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000
kfdhdb.spfile:                       16 ; 0x0f4: 0x00000010
kfdhdb.spfflg:                        1 ; 0x0f8: 0x00000001
In the output above, we see that
     the device /dev/sdb3 contains a copy of the ASM spfile (spfflg=1).
     The ASM spfile location starts at the disk offset of 16 (spfile=16)
Considering the allocation unit size (kfdhdb.ausize = 1M), let’s dump the ASM spfile from the device:
[root@host01 ~]#  dd if=/dev/sdb3 of=spfileASM_Copy2.ora skip=16  bs=1M count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.170611 seconds, 6.1 MB/s
[root@host01 ~]# strings spfileASM_Copy2.ora
+ASM1.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value
+ASM2.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value
+ASM3.__oracle_base=’/u01/app/grid’#ORACLE_BASE set from in memory value
+ASM3.asm_diskgroups=’FRA’#Manual Mount
+ASM2.asm_diskgroups=’FRA’#Manual Mount
+ASM1.asm_diskgroups=’FRA’#Manual Mount
*.asm_power_limit=1
*.diagnostic_dest=’/u01/app/grid’
*.instance_type=’asm’
*.large_pool_size=12M
*.remote_login_passwordfile=’EXCLUSIVE’
a:O/
 The same technique is used to access the Clusterware voting files which are also stored in an ASM disk group. In this case, Clusterware does not need a running ASM instance to access the cluster voting files:
Let’s check the location of voting disk :
[grid@host01 peer]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
–  —–    —————–                ——— ———
 1. ONLINE   243ec3b2a3cf4fbbbfed6f20a1ef4319 (ORCL:ASMDISK01) [DATA]
Located 1 voting disk(s).
– Since above query shows that voting disk is stored on ASMDISK01 which maps to /dev/sdb1,
   we will scan the header of /dev/sdb1
[root@host01 ~]#  kfed read /dev/sdb1 | grep vf
kfdhdb.vfstart:                      96 ; 0x0ec: 0x00000060
kfdhdb.vfend:                       128 ; 0x0f0: 0x00000080
Here we can see that voting disk resides on /dev/sdb1 .
Once the voting disk is accessible and ASM is started using the SPfile read above, rest of the resources on the node can be started after reading the Oracle Local Registry (OLR) on the node.

Convert NON RAC Database to RAC Database using RCONFIG

Oracle RAC 11G :

Oracle supports the following methods to convert a single-instance database to an RAC database as long as the RAC and the standalone environments are running on the same operating system and using the same oracle release:
• DBCA
• Oracle Enterprise Manager (grid control)
• RCONFIG
• Manual method
  
During the conversion, rconfig performs the following steps automatically:
• Migrating the database to ASM, if specified
• Creating RAC database instances on all specified nodes in the cluster
• Configuring the Listener and NetService entries
• Registering services with CRS
• Starting up the instances and listener on all nodes
 
In Oracle 11g R2., a single-instance database can either be converted to an administrator-managed cluster database or a policy-managed cluster database.

When you navigate through the $ORACLE_HOME/assistants/rconfig/sampleXMLS,
you will find two sample XML input files.
- ConvertToRAC_AdminManaged.xml
- ConvertToRAC_PolicyManaged.xml
 
While converting a single-instance database, with filesystem storage, to an RAC database with Automatic Storage Management (ASM), rconfig invokes RMAN internally to back up the database to proceed with converting non-ASM to ASM. 
Therefore, configuring parallel options to use multiple RMAN channels. In the RMAN on the local node may make backup run faster, which eventually reduces the conversion duration.
 
For example, you may configure the following in the RMAN  settings of orcl database on the local node.

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 6;
CURRENT SCENARIO:
- 3 node RAC setup
- Names of nodes : Host01, Host02, Host03
- Name of single instance database with filesystem storage : orcl
- Source Oracle home : /u01/app/oracle/product/11.2.0/dbhome_1
- Target Oracle home : /u01/app/oracle/product/11.2.0/dbhome_1

OBJECTIVE
- convert orcl to a Admin managed RAC database running on two nodes host01 and host02.
- change storage to ASM with
   . Datafiles on +DATA diskgroup
   . Flash recovery area on +FRA diskgroup
 
IMPLEMENTATION:
– copy ConvertToRAC_AdminManaged.xml to another file my.xml
host01$cd $ORACLE_HOME/assistants/rconfig/ sampleXMLs
host01$cp ConvertToRAC_AdminManaged.xml my.xml
 
– Edit my.xml and make following changes :
   . Specify current OracleHome of non-rac database for SourceDBHome
   . Specify OracleHome where the rac database should be configured. It can be same as SourceDBHome
   . Specify SID of non-rac database and credential. User with sysdba role is required to perform conversion
   . Specify the list of nodes that should have rac instances running for the Admin Managed Cluster Database. LocalNode should be the first node in this nodelist.
   . Instance Prefix tag is optional starting with 11.2. If left empty, it is derived from db_unique_name
   . Specify the type of storage to be used by rac database. Allowable values are CFS|ASM
   . Specify Database Area Location to be configured for rac database.
   . Specify Flash Recovery Area to be configured for rac database.


Note:
3 Options - YES/NO/ONLY
Database must be running.

[oracle@rac1 sampleXMLs]$ rconfig ctora.xml
<?xml version="1.0" ?>
<RConfig version="1.1" >
<ConvertToRAC>
    <Convert>
      <Response>
        <Result code="0" >
          Operation Succeeded
        </Result>
      </Response>
      <ReturnValue type="object">
There is no return value for this step     </ReturnValue>
    </Convert>
  </ConvertToRAC></RConfig>

– Run rconfig to convert orcl from single instance database to 2 instance RAC database
host01$rconfig my.xml
 
– Check the log file for rconfig while conversion is going on
oracle@host01$ls -lrt  $ORACLE_BASE/cfgtoollogs/rconfig/*.log
$tail -f
 
– check that the database has been converted successfully
host01$srvctl status database -d orcl
Instance orcl1 is running on node host01
Instance orcl2 is running on node host02
 
– Note that rconfig adds password file to all the nodes but  entry to   tnsnames.ora needs to be modified (to reflect scan name instead of host-ip) on the local node and added to rest of the nodes.
 
– For all other nodes, copy the entry for the database (orcl) from    tnsnames.ora on local node to tnsnames.ora on remote node(s).
 
– Following is the entry I modified on the local node and copied to rest of the nodes :
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster01-scan.cluster01.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
– check that database can be connected remotely from remote node.
host02$sqlplus system/oracle@orcl
 
– check that datafiles have converted to ASM
SQL>select name from v$datafile;
NAME
——————————————————————————–
+DATA/orcl/datafile/system.326.794838279
+DATA/orcl/datafile/sysaux.325.794838349
+DATA/orcl/datafile/undotbs1.305.794838405
+DATA/orcl/datafile/users.342.794838413
+DATA/orcl/datafile/undotbs2.348.794838
——————————————————————————




Saturday, 16 July 2016

Oracle Database Switch Over and Switch Back

Switch Over & Switch BACK STEP at Primary & DR (Standby server) manual. Before performing activity bounce both the instance (oracl...