Oracle Masters http://www.oraclemasters.in Oracle FAQ, Tips And Tricks, Resources, Certifications....Anything Oracle! Fri, 17 Feb 2012 03:55:03 +0000 en hourly 1 http://wordpress.org/?v=3.3.1 RMAN catalog http://www.oraclemasters.in/?p=1406 http://www.oraclemasters.in/?p=1406#comments Fri, 17 Feb 2012 03:55:03 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1406 Hi Ramesh,
I am moving from oracle 10g RAC database with user managed backup setup to oracle11gR2 RAC database with ASM and RMAN backup setup. My question is related to RMAN catalog database setup

1) The target database would be 11GR2.
2) For backup and recovery we will use RMAN.
3) entire setup is virtualised, we are using VM instead of bare metal or dedicated physical boxes.

My question

1.To create RMAN backup and recovery setup in this environment do i have to use another VM to create catalog database in which the target 11GR2 database would be registered.

2. What version of catalog database would be compatible with 11Gr2.

Let me know if i am missing any piece.

Would be glad to hear back.

Thanks
Amol
(amoljoshi_007@yahoo.com)

]]> http://www.oraclemasters.in/?feed=rss2&p=1406 1 RMAN http://www.oraclemasters.in/?p=1401 http://www.oraclemasters.in/?p=1401#comments Sat, 24 Dec 2011 16:04:37 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1401 Hi Ramesh,

I have one query regarding RMAN restoration.

I have backupieces of 3 days existing in disk,Now i want to restore 3 days old backup

As by default if you do resoratin it will restore latest backup,But i want to restore 3 day old backup,suppose I have backup of 17th,18th,19th dec and now I want to restore backup of 17th dec.

Please guide me how to do that.

Thanks

Tarun

#……Do not post your contact details in public

]]> http://www.oraclemasters.in/?feed=rss2&p=1401 1 Querry Regaring Registering Database With Catalog http://www.oraclemasters.in/?p=1397 http://www.oraclemasters.in/?p=1397#comments Sun, 18 Dec 2011 03:56:13 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1397 Hi,

I have seen the below link:-

for create Rman Catalog on 11g and registering 10G database which is on another server.

Overall it is great vedio to get an breif about catalog creation.

However, i have one confusion, after creating catalog on other server with 11g

When we are registering the 10g Database

We Just typed register database and we did not mention the name of catalog database.

My Question is how the target database knows that on which database its going to register.

Thanks,

Regards
Rohit Misri
Oracle DBA 10g

]]> http://www.oraclemasters.in/?feed=rss2&p=1397 1 Query running slow http://www.oraclemasters.in/?p=1363 http://www.oraclemasters.in/?p=1363#comments Thu, 22 Sep 2011 07:23:57 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1363 Hi Ramesh,

I have one question,That query is running fine yesterday and today is running slow.
now my question is how we can find explain plan of that query for yesterday one.

Also please explain what is verison counts.

Please answers.
Thank you
Tarun

]]> http://www.oraclemasters.in/?feed=rss2&p=1363 1 Restore directly from a backup piece using dbms_backup_restore http://www.oraclemasters.in/?p=1335 http://www.oraclemasters.in/?p=1335#comments Sat, 21 May 2011 08:11:27 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1335

The article describes how you could directly read a backup piece using the dbms_backup_restore package.

This restore is done from tape using the Netbackup MML.

declare
        v_dev   varchar2(50);
        v_done  boolean;
        v_backup_piece  varchar2(256);
begin
        -- Allocate Device
        v_dev := dbms_backup_restore.deviceallocate( type=>'sbt_tape', params=>'ENV=(NB_ORA_SERV=poltfmsbkp01,NB_ORA_CLIENT=POLTFSOLDBP02)');
 
        -- Set the backup piece information
        v_backup_piece :=  '/LTFP02_0_dly_14807_1_718596223';
 
        -- Start the backup conversation
        sys.dbms_backup_restore.restoreSetDatafile;
 
        -- Specify location of datafile
        sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>29,toname=>'/pool6/exportbkpU03/LTFU06/indx16k.dbf');
 
        -- Restore the datafile
        sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,handle=>v_backup_piece);
 
        if v_done THEN
 
                dbms_output.put_line('File restored successfully');
        else
                dbms_output.put_line('Some error');
        end if;
 
        sys.dbms_backup_restore.deviceDeallocate;
 
end;

You can even catalog the backup pieces using the

CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '/LTFP02_0_dly_14807_1_718596223';
or 
using dbms_backup_restore.inspectBackupPiece procedure

Once cataloged, you can restore it normally using restore command.

]]> http://www.oraclemasters.in/?feed=rss2&p=1335 0 Stored Outlines http://www.oraclemasters.in/?p=1329 http://www.oraclemasters.in/?p=1329#comments Wed, 04 May 2011 11:09:40 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1329

You can use a stored outline that allows use of a standard execution plan to be maintained, irrespective of the changes
in the system. This feature gives plan stability for queries. This can be useful when you cannot change the code as it
is a third party application, but feel that a certain query can run better with a particular execution plan.

The following article shows the technical aspects of setting up outlines in Oracle database.

  1. Setup environment for stored outlines
  2. Identify the ‘Good’ execution plan for the sql
  3. Create the stored outline
  4. Use the stored outline and verify

Setup environment for stored outlines

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SQL> show parameter stored
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
 
 
SQL> alter system set create_stored_outlines=TRUE;
 
System altered.
 
SQL> alter system set query_rewrite_enabled=TRUE;
 
System altered.

Identify the ‘Good’ execution plan for the sql

Let us assume that we are running the following query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

SQL> variable trn_id number;
SQL> exec :trn_id := 100;
 
PL/SQL procedure successfully completed.
 
SQL> select trn_id, trn_dt, trn_smn_id, trn_loc, trn_itm_sr_no, trn_itm_code, trn_itm_qty, trn_itm_price from sales_dtls where trn_id = :trn_id
  2  ;
 
    TRN_ID TRN_DT    TRN_SMN_ID    TRN_LOC TRN_ITM_SR_NO TRN_ITM_CODE         TRN_ITM_QTY TRN_ITM_PRICE
---------- --------- ---------- ---------- ------------- -------------------- ----------- -------------
       100 13-AUG-06         54         22             1 ABC 1                          7            19
       100 13-AUG-06         54         22             2 ABC 2                         43             5
       100 13-AUG-06         54         22             3 ABC 3                         59             8
       100 13-AUG-06         54         22             4 ABC 4                         80            18
       100 13-AUG-06         54         22             5 ABC 5                         10            15
       100 13-AUG-06         54         22             6 ABC 6                         97             9
       100 13-AUG-06         54         22             7 ABC 7                         15            15
       100 13-AUG-06         54         22             8 ABC 8                         75            10
       100 13-AUG-06         54         22             9 ABC 9                          6            18
       100 13-AUG-06         54         22            10 ABC 10                        15             6
       100 13-AUG-06         54         22            11 ABC 11                        61             7
       100 13-AUG-06         54         22            12 ABC 12                        50            18
       100 13-AUG-06         54         22            13 ABC 13                        28             3
       100 13-AUG-06         54         22            14 ABC 14                         6            15
       100 13-AUG-06         54         22            15 ABC 15                        75             8
       100 13-AUG-06         54         22            16 ABC 16                        84            19
       100 13-AUG-06         54         22            17 ABC 17                        85             4
       100 13-AUG-06         54         22            18 ABC 18                         3             4
       100 13-AUG-06         54         22            19 ABC 19                        14            17
       100 13-AUG-06         54         22            20 ABC 20                        48             6
       100 13-AUG-06         54         22            21 ABC 21                        24            16
       100 13-AUG-06         54         22            22 ABC 22                         5             2
       100 13-AUG-06         54         22            23 ABC 23                        41            17
       100 13-AUG-06         54         22            24 ABC 24                        75             7
       100 13-AUG-06         54         22            25 ABC 25                        94            14
 
25 rows selected.
 
SQL>

Get the hash_value from V$SQL as follows. Ensure the child number is the correct one for the plan
that you plan to create a stored outline for. (A sql may have multiple child cursors, for different
reasons. The V$SQL_SHARED_CURSOR view will help you understand why this happened.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SQL> col sql_text format a50
SQL> select sql_id, sql_text, hash_value, child_number
from v$sql
where sql_text like '%where trn_id = :trn_id%'
  2    3    4  ;
 
SQL_ID        SQL_TEXT                                           HASH_VALUE CHILD_NUMBER
------------- -------------------------------------------------- ---------- ------------
5m76djsz89357 select trn_id, trn_dt, trn_smn_id, trn_loc, trn_it 1048874151            0
              m_sr_no, trn_itm_code, trn_itm_qty, trn_itm_price
              from sales_dtls where trn_id = :trn_id
 
3fj5c8r59agz4 select sql_id, sql_text, hash_value, child_number  3398778852            0
              from v$sql where sql_text like '%where trn_id = :t
              rn_id%'
 
 
2 rows selected.

Create the stored outline

1
2
3

SQL> exec dbms_outln.create_outline(1048874151,0,'TEST_OUTLINES');
 
PL/SQL procedure successfully completed.

Use the stored outline

Get Outline information

1
2
3
4
5
6
7
8
9
10
11
12

SQL> select name, category, used, enabled, sql_text from user_outlines
where category='TEST_OUTLINES'
  2    3  ;
 
NAME                           CATEGORY                       USED   ENABLED  SQL_TEXT
------------------------------ ------------------------------ ------ -------- --------------------------------------------------
SYS_OUTLINE_11050416050420644  TEST_OUTLINES                  UNUSED ENABLED  select trn_id, trn_dt, trn_smn_id, trn_loc, trn_it
                                                                              m_sr_no, trn_itm_code, trn_itm_qty, trn_itm_price
                                                                              from sales_dtls where trn_id = :trn_id
 
 
1 row selected.

Set the outline for the session/system level. The outline will be used only after this value is set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

alter session set use_stored_outline=TEST_OUTLINES
 
Execute the query ----
@qq02
 
 
SQL> @qq03
 
NAME                           USED   CATEGORY                       SQL_TEXT                                           ENABLED
------------------------------ ------ ------------------------------ -------------------------------------------------- --------
SYS_OUTLINE_11050416050420644  USED   TEST_OUTLINES                  select trn_id, trn_dt, trn_smn_id, trn_loc, trn_it ENABLED
                                                                     m_sr_no, trn_itm_code, trn_itm_qty, trn_itm_price
                                                                     from sales_dtls where trn_id = :trn_id
 
 
1 row selected.

You can also check using the dbms_xplan.display_cursor function as follows

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

  1* select * from table(dbms_xplan.display_cursor('5m76djsz89357',0, 'OUTLINE'))
SQL> /
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5m76djsz89357, child number 0
-------------------------------------
select trn_id, trn_dt, trn_smn_id, trn_loc, trn_itm_sr_no,
trn_itm_code, trn_itm_qty, trn_itm_price from sales_dtls where trn_id =
:trn_id
 
Plan hash value: 2934699942
 
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   658 (100)|          |
|*  1 |  TABLE ACCESS FULL| SALES_DTLS |  1958 |   189K|   658   (2)| 00:00:08 |
--------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "SALES_DTLS"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("TRN_ID"=:TRN_ID)
 
Note
-----
   - outline "SYS_OUTLINE_11050415534809234" used for this statement
 
 
37 rows selected.
]]> http://www.oraclemasters.in/?feed=rss2&p=1329 0 Explain plan http://www.oraclemasters.in/?p=1325 http://www.oraclemasters.in/?p=1325#comments Wed, 04 May 2011 05:24:58 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1325 Hi Ramesh,

I have good explain plan of query.How to save that plan in Oracle dataabse,so that if some for reason plan change and query behaves bad,then I can load that good plan for particular query.

So question is how should I tell to optimizer to used this plan.

Thanks
Tarun

]]> http://www.oraclemasters.in/?feed=rss2&p=1325 8 Oracle 11g R2 SCAN Demo http://www.oraclemasters.in/?p=1296 http://www.oraclemasters.in/?p=1296#comments Mon, 10 Jan 2011 09:52:00 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1296

With releases prior to Oracle 11GR2, addition or deletion of nodes would entail the client tnsnames.ora file being updated with the correct VIP address. This would be cumbersome if there are many clients that connect to the cluster. With Oracle 11gR2 GRID, Oracle has introduced the concept of SCAN.Clients connect to the cluster providing the database service, via the scan listener (initial connection) and based on the load profile of the node, the scan listener would “hand off” the connection to the local listener, establishing the final database connection.

Confusing? May be because it is a new concept. However, with the following demonstration, I would help to clear a few fundamental doubts. As any document would state, SCAN can be implemented using

  1. Domain Name Server (Method used in this demo)
  2. Oracle provided Global Name Service (GNS)

For the demo, I have used Vmware to host 2 nodes running Solaris 10 (x86 64bit). The vmware software is installed on a Windows server which will also serve as the DNS server. The details of the configuration are as below

Node 1 Node 2
Public 192.168.1.200 (rac11gr2-n1.vmhosts-demo.com) Public 192.168.1.202 (rac11gr2-n2.vmhosts-demo.com)
VIP 192.168.1.201 (rac11gr2-n1-vip.vmhosts-demo.com) VIP 192.168.1.203 (rac11gr2-n2-vip.vmhosts-demo.com)
Private 10.10.10.1 (rac11gr2-n1-pri.vmhosts-demo.com) Private 10.10.10.2 (rac11gr2-n2-pri.vmhosts-demo.com)

The Windows Server IP is 192.168.1.2 and dns would be running on port 53 (by default) As mentioned earlier, we need 3 IPs (recommended by Oracle) which have not been allocated to any server. These 3 IPs must be “resolved” via a DNS request. During the cluster installation, the scan ip will be resolved, and the 3 IPs will be “plugged” in to the public interface of the cluster nodes. I have reserved the following three IPs for SCAN 192.168.1.222, 192.168.1.223, 192.168.1.224

Step 1 : Setup DNS Entries

In this step, you must have the DNS role enabled for your windows server. If you are not using a Windows Server, you can download DNS software over the net and configure it. This demo shows setting up a DNS server and making SCAN IP entries.

Note: The demo is more inclined towards understanding the SCAN concept than the DNS setup. So, if you have other professional methods for doing the same, kindly follow those.

Create a vmhosts-demo.com domain

Now add the hosts by right click and add host on the vmhosts-demo.com zone


Add the first entry

Repeat the entries with the same scan name and different IP Address


Ensure that you have round robin enabled (for load balancing , the poor man's load balancer)

Step 2 :Setup names resolution for solaris nodes

Once you have setup the DNS entries, you can use nslookup from the a Command Window and check if the name is resolvable. The round robin enabler also ensures that the IP returned is different in the subsequent nslookup.s

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

C:\Users\Administrator.HOMESERVER>nslookup scan-ip.vmhosts-demo.com
1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa
        primary name server = 1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.
0.0.0.0.0.0.0.ip6.arpa
        responsible mail addr = (root)
        serial  = 0
        refresh = 28800 (8 hours)
        retry   = 7200 (2 hours)
        expire  = 604800 (7 days)
        default TTL = 86400 (1 day)
Server:  UnKnown
Address:  ::1
 
DNS request timed out.
    timeout was 2 seconds.
DNS request timed out.
    timeout was 2 seconds.
Name:    scan-ip.vmhosts-demo.com
Addresses:  192.168.1.224
          192.168.1.222
          192.168.1.223
 
C:\Users\Administrator.HOMESERVER>nslookup scan-ip.vmhosts-demo.com
1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa
        primary name server = 1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.
0.0.0.0.0.0.0.ip6.arpa
        responsible mail addr = (root)
        serial  = 0
        refresh = 28800 (8 hours)
        retry   = 7200 (2 hours)
        expire  = 604800 (7 days)
        default TTL = 86400 (1 day)
Server:  UnKnown
Address:  ::1
 
DNS request timed out.
    timeout was 2 seconds.
Name:    scan-ip.vmhosts-demo.com
Addresses:  192.168.1.222
          192.168.1.223
          192.168.1.224

However, in order for the solaris nodes to resolve the scan ip, you must make the following entries on both the nodes in these files

  • /etc/nsswitch.conf

Add dns as resolution method

1

hosts:      files dns</blockquote>
  • /etc/resolv.conf
1
2

domain vmhosts-demo.com
nameserver 192.168.1.2  # This is the DNS Server that will resolve your scan IP

Step 3: Validate SCAN IPs

Run the nslookup command from both the nodes 

1
2
3
4
5
6
7
8
9
10
11

# nslookup scan-ip.vmhosts-demo.com
Server:         192.168.1.2
Address:        192.168.1.2#53
 
Name:   scan-ip.vmhosts-demo.com
Address: 192.168.1.222
Name:   scan-ip.vmhosts-demo.com
Address: 192.168.1.224
Name:   scan-ip.vmhosts-demo.com
Address: 192.168.1.223
#

Step 4 :Install and Configure CRS and RAC database

Once you have configured your SCAN DNS entries and names resolution for the solaris nodes, you can proceed with the installation of the CRS. You just have to mention the SCAN IP in one of the screens during the installation. The scan ip will be validated and the if all is ok, configuration will be automatic.
When you run root.sh on the first node, all the 3 scan IPs are plugged on to the first node’s public interface as shown below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# hostname
rac11gr2-n1.vmhosts-demo.com
 
# ifconfig -a
lo0: flags=2001000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4,VIRTUAL> mtu 8232 index 1
        inet 127.0.0.1 netmask ff000000
e1000g0: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2
        inet 192.168.1.200 netmask ffffff00 broadcast 192.168.1.255
        ether 0:c:29:37:da:14
e1000g0:1: flags=1040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4> mtu 1500 index 2
        inet 192.168.1.201 netmask ffffff00 broadcast 192.168.1.255
e1000g0:2: flags=1040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4> mtu 1500 index 2
        inet 192.168.1.224 netmask ffffff00 broadcast 192.168.1.255
e1000g0:3: flags=1040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4> mtu 1500 index 2
        inet 192.168.1.223 netmask ffffff00 broadcast 192.168.1.255
e1000g0:4: flags=1040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4> mtu 1500 index 2
        inet 192.168.1.222 netmask ffffff00 broadcast 192.168.1.255
e1000g1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 3
        inet 10.10.10.1 netmask ff000000 broadcast 10.255.255.255
        ether 0:c:29:37:da:1e
#

Once you run root.sh on the second node, one of the scan ip is moved to the second node 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# hostname
rac11gr2-n1.vmhosts-demo.com
# ifconfig -a
lo0: flags=2001000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4,VIRTUAL> mtu 8232 index 1
        inet 127.0.0.1 netmask ff000000
e1000g0: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2
        inet 192.168.1.200 netmask ffffff00 broadcast 192.168.1.255
        ether 0:c:29:37:da:14
e1000g0:1: flags=1040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4> mtu 1500 index 2
        inet 192.168.1.201 netmask ffffff00 broadcast 192.168.1.255
e1000g0:3: flags=1040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4> mtu 1500 index 2
        inet 192.168.1.223 netmask ffffff00 broadcast 192.168.1.255
e1000g0:4: flags=1040843<UP,BROADCAST,RUNNING,MULTICAST,DEPRECATED,IPv4> mtu 1500 index 2
        inet 192.168.1.222 netmask ffffff00 broadcast 192.168.1.255
e1000g1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 3
        inet 10.10.10.1 netmask ff000000 broadcast 10.255.255.255
        ether 0:c:29:37:da:1e

Since we are using DNS, de select GNS


Step 5 :Test SCAN using client connection

This test uses a 11gR2 client. We will discuss pre 11gR2 clients later. You need to make an entry in the tnsnames.ora of the client

Let us see what are the settings on the server, with respect to services and listeners

  1. LOCAL AND REMOTE LISTENER SETTINGS

    2. Node 1

    1
2
3
4
5
6
7
8
9

    SQL> show parameter _listener
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=rac1
                                                 1gr2-n1-vip.vmhosts-demo.com)(
                                                 PORT=1521))))
remote_listener                      string      scan-ip.vmhosts-demo.com:1521

    Node 2

    1
2
3
4
5
6
7
8
9

    SQL> show parameter _listener
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(AD
                                                 DRESS=(PROTOCOL=TCP)(HOST=rac1
                                                 1gr2-n2-vip.vmhosts-demo.com)(
                                                 PORT=1521))))
remote_listener                      string      scan-ip.vmhosts-demo.com:1521
  2. Status of local listeners

    3. Node 1

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

    $ lsnrctl status
 
LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 10-JAN-2011 16:15:56
 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
Start Date                10-JAN-2011 10:47:50
Uptime                    0 days 5 hr. 28 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/app/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /crs/oracle/product/app/diag/tnslsnr/rac11gr2-n1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.200)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.201)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "PROD.vmhosts-demo.com" has 1 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
Service "PRODXDB.vmhosts-demo.com" has 1 instance(s).
  Instance "PROD1", status READY, has 0 handler(s) for this service...
The command completed successfully
$

    Node 2

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

    $ lsnrctl status
 
LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 10-JAN-2011 16:15:19
 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
Start Date                10-JAN-2011 10:51:14
Uptime                    0 days 5 hr. 24 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/app/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /crs/oracle/product/app/diag/tnslsnr/rac11gr2-n2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.202)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.203)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "PROD.vmhosts-demo.com" has 1 instance(s).
  Instance "PROD2", status READY, has 1 handler(s) for this service...
Service "PRODXDB.vmhosts-demo.com" has 1 instance(s).
  Instance "PROD2", status READY, has 0 handler(s) for this service...
The command completed successfully
$
  3. Configuration of SCAN Listeners 
    4. 1
2
3
4
5
6
7
8
9
10
11
12
13

    $ srvctl config scan
SCAN name: scan-ip.vmhosts-demo.com, Network: 1/192.168.1.0/255.255.255.0/e1000g0
SCAN VIP name: scan1, IP: /192.168.1.224/192.168.1.224
SCAN VIP name: scan2, IP: /192.168.1.223/192.168.1.223
SCAN VIP name: scan3, IP: export ORACLE_HOME=/crs/oracle/product/11.2.0/grid/192.168.1.222/192.168.1.222
 
$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node rac11gr2-n2
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node rac11gr2-n1
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node rac11gr2-n1
  4. Check status of SCAN Listeners

    To do this set the ORACLE_HOME and GRID_HOME as follows
    export ORACLE_HOME=/crs/oracle/product/11.2.0/grid (Location of the grid home)
    export GRID_HOME=$ORACLE_HOME

    On Node 1

    1
2
3
4
5
6
7
8
9
10
11

     $ lsnrctl status LISTENER_SCAN1
 
 LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 10-JAN-2011 16:20:20
 
 Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
 TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Solaris Error: 2: No such file or directory

    You got the above error because the SCAN1 listener is running on Node 2

    Ensure that you get result for all the SCAN listeners on nodes where they are running
    Now on Node 1

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

    $ lsnrctl status LISTENER_SCAN2
 
LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 10-JAN-2011 16:20:51
 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN2
Version                   TNSLSNR for Solaris: Version 11.2.0.1.0 - Production
Start Date                10-JAN-2011 10:47:51
Uptime                    0 days 5 hr. 33 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /crs/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File         /crs/oracle/product/11.2.0/grid/log/diag/tnslsnr/rac11gr2-n1/listener_scan2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.223)(PORT=1521)))
Services Summary...
Service "PROD.vmhosts-demo.com" has 2 instance(s).
  Instance "PROD1", status READY, has 1 handler(s) for this service...
  Instance "PROD2", status READY, has 1 handler(s) for this service...
Service "PRODXDB.vmhosts-demo.com" has 2 instance(s).
  Instance "PROD1", status READY, has 0 handler(s) for this service...
  Instance "PROD2", status READY, has 0 handler(s) for this service...
The command completed successfully

Now that all the entries are working fine, Let us make an entry in tnsnames.ora of the client machine. This entry is specific to 11GR2 release.

1
2
3
4
5
6
7
8
9
10

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST=scan-ip.vmhosts-demo.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD.vmhosts-demo.com)
    )
  )

Finally, you can make a connection from the client using SCAN. However, if you hit a bug where you get ORA-12502, ORA-12564, this is because you
cannot resolve the VIP address from the client. This can be resolved by either adding the ip address and hostname in the client’s /ect/hosts file or by
hardcoding the LOCAL_LISTENER with the VIP IP Address in the cluster database spfile for both nodes. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

$  sqlplus "system/oracle@prod"
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 10 16:07:53 2011
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
PROD1
 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Connecting again…it now goes to a different instance (load balancing is working)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

$  sqlplus "system/oracle@prod"
 
 
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 10 16:08:13 2011
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
PROD2
 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
]]> http://www.oraclemasters.in/?feed=rss2&p=1296 0 How to Repair Corrupt Oracle Installation http://www.oraclemasters.in/?p=1277 http://www.oraclemasters.in/?p=1277#comments Thu, 18 Nov 2010 16:36:28 +0000 tdomf_2a078 http://www.oraclemasters.in/?p=1277 Good Day Oracle Masters,

Hope u are well.

I have some problem with my Oracle Installation, we had a virus called Sality and I think it might have
affected some of my Oracle system files. After we installed Karspersky AntiVirus 6.0.4,
I now can’t launch any of the Oracle Tools, like Enterprise Manager Console, Net Service Configuration, SQLPlus etc.

But surprisingly when I check the Oracle Services, they are started, but I am unable to access the DB via SQLPLUS nor EM Console, nor can
I reconfigure the TNS.
So am wondering what to do next, could you possibly know of a way I can fix this kind of a problem?
My Data files .i.e .DBF files are fortunately on a separate drive (D:), but I wonder if there
is a way I can recover the affected Oracle system files and then restore the data.

Please guide me on how to go about this issue.

thanks&regards,

NB:
When you insert the Oracle 10g R2 CD, is there an option to Repair?

]]> http://www.oraclemasters.in/?feed=rss2&p=1277 1 Oracle 11g : Real Time Query/Active Dataguard http://www.oraclemasters.in/?p=1265 http://www.oraclemasters.in/?p=1265#comments Sat, 11 Sep 2010 14:28:32 +0000 Ramesh Menon http://www.oraclemasters.in/?p=1265

With Oracle 11g, you can use physical standby database while your it is open in read only mode. Hence, the users can get real time data, which is almost as of production time. This feature is called Active Dataguard or Real Time Query.

This feature is part of the Oracle Enterprise edition and requires separate license for use.

Usage of this feature is pretty simple and straight forward. All you need to do is :

  1. Create standby redo logfiles on the physical standby for real time apply
  2. Take out the standby from recovery mode
  3. Open standby database in read only mode
  4. Start real time apply recovery on the physical standby
  5. Test the setup

Create standby redo logfiles for the standby database

1
2
3
4
5
6
7
8
9

SQL> alter database add standby logfile '/oradata11g/PROD11R2/data01/srl01.log' size 100M;
 
Database altered.
 
SQL> alter database add standby logfile '/oradata11g/PROD11R2/data01/srl02.log' size 100M;
 
Database altered.
 
SQL>

Stop recovery on the physical standby database

1
2
3

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

Open physical standby database in read only mode

1
2
3
4
5
6
7
8
9
10
11

SQL> alter database open;
 
Database altered.
 
SQL> select name, open_mode, controlfile_type from v$database;
 
NAME      OPEN_MODE            CONTROL
--------- -------------------- -------
PROD11R2  READ ONLY            STANDBY
 
SQL>

Start real time apply recovery on physical standby

1
2
3

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

Test the setup

On the PRIMARY database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

SQL> conn scott/tiger
Connected.
SQL> select ename, empno, sal from emp
  2  where empno = 7934;
 
ENAME           EMPNO        SAL
---------- ---------- ----------
MILLER           7934       1300
 
SQL> update EMP
  2  set sal = sal * 1.25
  3  where  empno = 7934;
 
1 row updated.
 
SQL> commit;
 
Commit complete.
 
SQL>  select ename, empno, sal from emp
 where empno = 7934;  2
 
ENAME           EMPNO        SAL
---------- ---------- ----------
MILLER           7934       1625
 
SQL>

On the STANDBY database

1
2
3
4
5
6
7
8
9
10
11
12

SQL> select * from emp
  2  where empno = 7934;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7934 MILLER     CLERK           7782 23-JAN-82       1625
        10
 
 
SQL>

As you can see, the data has been replicated to the standby database even when it is opened in READ ONLY mode. This is very useful when you want to run queries on standby with real time data of production.

]]> http://www.oraclemasters.in/?feed=rss2&p=1265 2