Logical Thinking: March 2014

Sunday, March 30, 2014

adding service to a database

You can see that there is no service attached to my database so that challenge is add service on primary and standby databases

[oracle@rac2 admin]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA
Mount point paths:
Services: Type: RAC
Database is administrator managed
[oracle@rac2 admin]$
[root@rac1 ~]# crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
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.rac.db
1 ONLINE ONLINE rac1 Open
2 ONLINE ONLINE rac2 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 ~]#

Add service on primary

[oracle@rac1 ~]$ srvctl add service -d RAC -s RAC_HA -r RAC1,RAC2 -P BASIC -l PRIMARY -q TRUE -e SELECT -m BASIC -w 10 -z 60
[oracle@rac1 ~]$
[oracle@rac1 ~]$
[oracle@rac1 ~]$ srvctl start service -d RAC -s RAC_HA
[oracle@rac1 ~]$
[oracle@rac2 admin]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA
Mount point paths:
Services: RAC_HA
Type: RAC
Database is administrator managed
[oracle@rac2 admin]$

Cluster resource status

[root@rac1 ~]# crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.FRA.dg
ONLINE ONLINE rac1
ONLINE ONLINE rac2
ora.LISTENER.lsnr
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.rac.db
1 ONLINE ONLINE rac1 Open
2 ONLINE ONLINE rac2 Open
ora.rac.rac_ha.svc
1 ONLINE ONLINE rac1
2 ONLINE ONLINE rac2
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 ~]#

On standby

[oracle@racdr1 admin]$ srvctl add service -d RAC_STBY -s RAC_HA -r RAC_STBY1 -P BASIC -l PRIMARY -q TRUE -e SELECT -m BASIC -w 10 -z 60
[oracle@racdr1 admin]$ srvctl config database -d RAC_STBY
Database unique name: RAC_STBY
Database name: rac
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC_STBY/parameterfile/spfileRAC_STBY.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: RAC_STBY
Database instances: RAC_STBY1
Disk Groups: DATA,FRA
Mount point paths:
Services: RAC_HA
Type: RAC
Database is administrator managed
[oracle@racdr1 admin]$
[oracle@racdr1 admin]$
[oracle@racdr1 admin]$ srvctl start service -d RAC_STBY -s RAC_HA
[oracle@racdr1 admin]$ srvctl config database -d RAC_STBY
Database unique name: RAC_STBY
Database name: rac
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC_STBY/parameterfile/spfileRAC_STBY.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: RAC_STBY
Database instances: RAC_STBY1
Disk Groups: DATA,FRA
Mount point paths:
Services: RAC_HA
Type: RAC
Database is administrator managed
[oracle@racdr1 admin]$

DNS Configuration for Oracle RAC on VirtualBox

Wondering how to setup DNS server which can be used for oracle RAC in your virtual box environment. Do not worry I will provide the steps on how to setup DNS.

In my case the setup was done on redhat linux

IP Address of DNS Server: 192.168.56.150
DNS Server name: dns1.localdomain
Domain Name: localdomain
Public Network for RAC: 192.168.56
Private Network for RAC: 192.168.1

Steps:
1. Install bind using yum
2. start named service
3. backup original confs if there are any
4. modify or create /etc/named.conf
5. Modify or create /var/named/56.168.192.in-addr.arpa
6. Modify or create zone /var/named/localdomain.zone
7. modify hosts file /etc/hosts
8. Restart named service

1. Install bind using yum

yum -y install bind-libs bind bind-utils

2. start named service

service named start
chkconfig named on

3. backup original confs if there are any

cp -p /etc/named.conf /etc/named.conf.original
cp -p /var/named/56.168.192.in-addr.arpa /var/named/56.168.192.in-addr.arpa.original
cp -p /var/named/localdomain.zone /var/named/localdomain.zone.original

4. modify or create /etc/named.conf

[root@dns1 ~]# cat /etc/named.conf
//
// named.conf
//
// Provided by Red Hat bind package to configure the ISC BIND named(8) DNS
// server as a caching only nameserver (as a localhost DNS resolver only).
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//
options {
listen-on port 53 { 127.0.0.1;192.168.56.150; };
listen-on-v6 port 53 { ::1; };
directory "/var/named";
dump-file "/var/named/data/cache_dump.db";
statistics-file "/var/named/data/named_stats.txt";
memstatistics-file "/var/named/data/named_mem_stats.txt";
allow-query { any; };
recursion yes;
dnssec-enable yes;
dnssec-validation yes;
dnssec-lookaside auto;
/* Path to ISC DLV key */
bindkeys-file "/etc/named.iscdlv.key";
managed-keys-directory "/var/named/dynamic";
};
logging {
channel default_debug {
file "data/named.run";
severity dynamic;
};
};
zone "." IN {
type hint;
file "named.ca";
};
zone "localdomain." IN {
type master;
file "localdomain.zone";
allow-update { none; };
};
zone "56.168.192.in-addr.arpa." IN {
type master;
file "56.168.192.in-addr.arpa";
allow-update { none; };
};

include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";
[root@dns1 ~]#

5. Modify or create /var/named/56.168.192.in-addr.arpa

[root@dns1 ~]# cat /var/named/56.168.192.in-addr.arpa
$ORIGIN 56.168.192.in-addr.arpa.
$TTL 1H
@ IN SOA dns1.localdomain. root.dns1.localdomain. ( 2
3H
1H
1W
1H )
56.168.192.in-addr.arpa. IN NS dns1.localdomain.
101 IN PTR rac1.localdomain.
102 IN PTR rac2.localdomain.
103 IN PTR rac1-vip.localdomain.
104 IN PTR rac2-vip.localdomain.
105 IN PTR scan.localdomain.
106 IN PTR scan.localdomain.
107 IN PTR scan.localdomain.
120 IN PTR host01.localdomain.
135 IN PTR vhost1.localdomain.
100 IN PTR rcat1.localdomain.
111 IN PTR racdr1.localdomain.
113 IN PTR racdr1-vip.localdomain.
115 IN PTR scandr.localdomain.
116 IN PTR scandr.localdomain.
117 IN PTR scandr.localdomain.
[root@dns1 ~]#

6. Modify or create zone /var/named/localdomain.zone

[root@dns1 ~]# cat /var/named/localdomain.zone
$TTL 86400
@ IN SOA localhost root.localhost (
42 ; serial (d. adams)
3H ; refresh
15M ; retry
1W ; expiry
1D ) ; minimum
IN NS localhost
localhost IN A 127.0.0.1
rac1 IN A 192.168.56.101
rac2 IN A 192.168.56.102
rac1-priv IN A 192.168.1.101
rac2-priv IN A 192.168.1.102
rac1-vip IN A 192.168.56.103
rac2-vip IN A 192.168.56.104
scan IN A 192.168.56.105
scan IN A 192.168.56.106
scan IN A 192.168.56.107
host01 IN A 192.168.56.120
vhost1 IN A 192.168.56.135
rcat1 IN A 192.168.56.100
racdr1 IN A 192.168.56.111
racdr1-priv IN A 192.168.1.111
racdr1-vip IN A 192.168.56.113
scandr IN A 192.168.56.115
scandr IN A 192.168.56.116
scandr IN A 192.168.56.117
[root@dns1 ~]#

7. modify hosts file /etc/hosts

[root@dns1 ~]# cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.56.150 dns1.localdomain dns1
# Public
192.168.56.101 rac1.localdomain rac1
192.168.56.102 rac2.localdomain rac2
# Private
192.168.1.101 rac1-priv.localdomain rac1-priv
192.168.1.102 rac2-priv.localdomain rac2-priv
# Virtual
192.168.56.103 rac1-vip.localdomain rac1-vip
192.168.56.104 rac2-vip.localdomain rac2-vip
# SCAN
192.168.56.105 scan.localdomain scan
192.168.56.106 scan.localdomain scan
192.168.56.107 scan.localdomain scan

192.168.56.120 host01.localdomain host01
192.168.56.100 rcat1.localdomain rcat1
#DR
# Public
192.168.56.111 racdr1.localdomain racdr1
# Private
192.168.1.111 racdr1-priv.localdomain racdr1-priv
# Virtual
192.168.56.113 racdr1-vip.localdomain racdr1-vip
# SCAN
192.168.56.115 scandr.localdomain scandr
192.168.56.116 scandr.localdomain scandr
192.168.56.117 scandr.localdomain scandr
[root@dns1 ~]#

8. Restart named service

service named restart

And you are done your dns is up and running now.

Thursday, March 27, 2014

DGMGRL show configuration reporting ORA-16810: multiple errors or warnings detected for the database

I was getting the error on primary an standby databases under show configuration .

DGMGRL> show configuration
Configuration - racdg
Protection Mode: MaxPerformance
Databases:
RAC - Primary database
Error: ORA-16810: multiple errors or warnings detected for the database
RAC_STBY - Physical standby database
Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

To get more details I looked at individual database

DGMGRL>
DGMGRL> show database 'RAC'
Database - RAC
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
RAC1
Error: ORA-16737: the redo transport service for standby database "RAC_STBY" has an error
RAC2
Error: ORA-16737: the redo transport service for standby database "RAC_STBY" has an error
Database Status:
ERROR
DGMGRL>

This prompted me to see the alert logs of primary database where I was getting error that primary is not able to connect to standby

***********************************************************************
Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=racdr1-vip)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC_STBY.localdomain)(CID=(PROGRAM=oracle)(HOST=rac1.localdomain)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 11.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0 - Production
Time: 27-MAR-2014 13:23:09
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
***********************************************************************

My immediate response was to see if the database on stanby is running or which was not

[oracle@racdr1 ~]$ srvctl status database -d RAC_STBY
Instance RAC_STBY1 is not running on node racdr1
[oracle@racdr1 ~]$

After starting the stand by database.

[oracle@racdr1 ~]$ srvctl start database -d RAC_STBY
[oracle@racdr1 ~]$

I was too quick on primary issuing the statement and found that oracle now knows that we are starting standby database.

DGMGRL> show configuration

Configuration - racdg
Protection Mode: MaxPerformance
Databases:
RAC - Primary database
RAC_STBY - Physical standby database
Error: ORA-16770: Redo Apply not started since physical standby database is opening
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

Once the database was started on standby all went good.

DGMGRL> show configuration
Configuration - racdg
Protection Mode: MaxPerformance
Databases:
RAC - Primary database
RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database 'RAC'
Database - RAC
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
RAC1
RAC2
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database 'RAC_STBY'
Database - RAC_STBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
RAC_STBY1
Database Status:
SUCCESS
DGMGRL>

Sunday, March 16, 2014

oracle how to gather stats, backup them and use the backed up stats

Oracle stats are used by optimiser to determine the query plan. Query plan is how the query will be executed by oracle.

Plan determine if the query will use full table scan of the table / use indexes / use hash join / nested loops etc to get the final outcome from the query. All of this depends on what the stats are and stats will hold the number of the rows in table, cardinality, size of data and many more.

It is not always good idea to gather stats on tables all the time something they may change the query plan which might not be optimal for your scenario. So if you want to gather stats then always a good idea to export the stats of that table and then gather stats.

In this post I will discuss on how to export and import stats.

Steps:
1. Create a table which will store the exported stats
2. Grant permission to use this table
3. Export the stats (schema , table, index etc. )
4. Gather new stats on object
5. If required import stats back from exported table

Type of exports you can do in oracle 11g
EXPORT_COLUMN_STATS
EXPORT_DATABASE_STATS
EXPORT_DICTIONARY_STATS
EXPORT_FIXED_OBJECTS_STATS
EXPORT_INDEX_STATS
EXPORT_SCHEMA_STATS
EXPORT_SYSTEM_STATS
EXPORT_TABLE_STATS

Check Current statistics:

select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where TABLE_NAME='EMP';

Create stats table:

EXEC DBMS_STATS.CREATE_STAT_TABLE (ownname=>'HDHILLON', stattab=>'STATS_SCHEMA_EMP', tblspace=>'MICHAEL');

Grant Permissions:

GRANT SELECT,INSERT,UPDATE,DELETE on CSU.STATS_SCHEMA_EMP TO PUBLIC;

Export statistics:

exec dbms_stats.export_schema_stats (ownname =>'HDHILLON',stattab=>'STATS_SCHEMA_EMP');

Gather stats for HDHILLON.EMP table:

execute dbms_stats.gather_table_stats(ownname=>'HDHILLON', tabname=>'EMP', estimate_percent => 10, method_opt => 'for all columns size skewonly', cascade=> true);

Check new statistics:

select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where TABLE_NAME='EMP';

Importing previously backed up stats:

exec dbms_stats.import_table_stats ('HDHILLON', 'EMP', stattab=>'STATS_SCHEMA_EMP');

Import procedures which can be used are:

IMPORT_COLUMN_STATS

IMPORT_DATABASE_STATS

IMPORT_DICTIONARY_STATS

IMPORT_FIXED_OBJECTS_STATS

IMPORT_INDEX_STATS

IMPORT_SCHEMA_STATS

IMPORT_SYSTEM_STATS

IMPORT_TABLE_STATS

Example of exporting table stats is:

exec DBMS_STATS.EXPORT_TABLE_STATS (ownname =>'HDHILLON', tabname => 'EMP', stattab=>'STATS_SCHEMA_EMP' );

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

Export fixed object stats

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

exec dbms_stats.create_stat_table('HDHILLON','HARVEY_STATS_TABLE','USERS');

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED FIXED_OBJECTS
-------------------- -------------
NO STATS 950

SQL>

SQL> select * from HARVEY_STATS_TABLE;

no rows selected

SQL>

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED FIXED_OBJECTS
-------------------- -------------
NO STATS 117
2015-Feb-20 833

SQL>

exec dbms_stats.export_fixed_objects_stats(stattab=>'HARVEY_STATS_TABLE',statown=>'HDHILLON');

SQL> select count(*) from HARVEY_STATS_TABLE;

COUNT(*)

----------

23578

SQL>

------------------
Import fixed object stats
------------------
select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED FIXED_OBJECTS
-------------------- -------------
NO STATS 117
2015-Feb-20 833

SQL>

exec dbms_stats.delete_fixed_objects_stats();

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED FIXED_OBJECTS

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

NO STATS 950

SQL>

exec dbms_stats.import_fixed_objects_stats(stattab=>'HARVEY_STATS_TABLE',statown=>'HDHILLON');

select NVL(TO_CHAR(last_analyzed, 'YYYY-Mon-DD'), 'NO STATS') last_analyzed, COUNT(*) fixed_objects
FROM dba_tab_statistics
WHERE object_type = 'FIXED TABLE'
GROUP BY TO_CHAR(last_analyzed, 'YYYY-Mon-DD')
ORDER BY 1 DESC;

LAST_ANALYZED FIXED_OBJECTS
-------------------- -------------
NO STATS 117
2015-Feb-20 833

SQL>

Ever want to drop the stats table

SQL> exec dbms_stats.drop_stat_table('HDHILLON','HARVEY_STATS_TABLE');

Syntax:

DBMS_STATS.DROP_STAT_TABLE (
   ownname VARCHAR2, 
   stattab VARCHAR2);

Friday, March 14, 2014

oracle 11g flashback data archive setup and should be using SCN for constant results

Every day we are getting more and more pressure to keep the historical data. We can do that by writing multiple scipts or at application level writing logic to do so.

But oracle can do this for you.

Problem: We have a table and there are lots of insert, update and deletes happening all times which is causing historic data to disappear from the table once the change is done. How can we use oracle to solve this problem.

Solution: Oracle offers a solution with version 11g and it is called flashback data archive. Once setup it will move the changes to the table to another table and manage it for the retention period.

How to setup Flash back archive:

1. Create a tablespace to hold flashback data archives
2. Create flashback archive with some retention (users need FLASHBACK ARCHIVE ADMINISTER privilege to create this archive.)
3. alter table to start archiving changes.
4. select data with AS OF {SCN of TIMESTAMP}
5. Done

Steps:

SQL> create tablespace fbdata datafile '/u01/app/oracle/oradata/new_location/fbdata01.dbf' size 2G;
10:33:34 SQL> create flashback archive fb_harvey tablespace fbdata retention 10 year;
Flashback archive created.
10:33:48 SQL> col FLASHBACK_ARCHIVE_NAME for a30
10:33:52 SQL> select flashback_archive_name, flashback_archive#,
10:33:52 2 retention_in_days,
10:33:52 3 status
10:33:52 4 from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS STATUS
------------------------------ ------------------ ----------------- -------
FB_HARVEY 1 3650
10:33:52 SQL>
10:33:52 SQL> set lines 60
10:34:06 SQL> desc hdhillon.status;
Name Null? Type
----------------------------- -------- --------------------
NO NUMBER
TYPE VARCHAR2(10 CHAR)
10:34:13 SQL> set lines 270
10:34:16 SQL> select * from hdhillon.status;
no rows selected
10:34:29 SQL> select * from dba_flashback_archive_tables;
no rows selected
10:34:40 SQL>
10:34:40 SQL> alter table hdhillon.status flashback archive fb_harvey;
Table altered.
10:34:45 SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS

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

STATUS HDHILLON FB_HARVEY SYS_FBA_HIST_129223 ENABLED
10:34:49 SQL>
10:35:00 SQL> conn hdhillon/*****
Connected.
10:35:16 SQL>
11:13:56 SQL> insert into status values(1,'working');
1 row created.
11:14:16 SQL>
11:14:16 SQL> commit;
Commit complete.
11:14:18 SQL> col current_timestamp for a40
11:14:18 SQL> select current_scn, current_timestamp(2) , no, type from v$database , status t;
CURRENT_SCN CURRENT_TIMESTAMP(2) NO TYPE
----------- --------------------------------------------------------------------------- ---------- ----------
132432317 14-MAR-14 11.14.22.43 AM +11:00 1 working
11:14:22 SQL>
11:18:20 SQL> delete from status where no =1;
1 row deleted.
11:18:39 SQL> commit;
Commit complete.
11:18:43 SQL> select current_scn, current_timestamp(2) ts , no, type from v$database , status t;
no rows selected
11:18:51 SQL> insert into status values(2,'Error');
1 row created.
11:19:02 SQL> commit;
Commit complete.
11:19:05 SQL> select current_scn, current_timestamp(2) ts , no, type from v$database , status t;
CURRENT_SCN TS NO TYPE
----------- ----------------------------------- ---------- ----------
132437486 14-MAR-14 11.19.12.15 AM +11:00 2 Error
11:19:12 SQL>
11:19:48 SQL> update status set type = 'Warning' where no =2;
1 row updated.
11:21:01 SQL> commit;
Commit complete.
11:21:03 SQL> select current_scn, current_timestamp(2) ts , no, type from v$database , status t;
CURRENT_SCN TS NO TYPE
----------- ----------------------------------- ---------- ----------
132437561 14-MAR-14 11.21.07.62 AM +11:00 2 Warning
11:21:07 SQL>

Now selecting data:

11:22:14 SQL> select * from status as of SCN 132432317;
NO TYPE
---------- ----------
1 working
11:35:00 SQL> select * from status as of SCN 132437486;
NO TYPE
---------- ----------
2 Error
11:37:14 SQL> select * from status as of SCN 132437561;
NO TYPE
---------- ----------
2 Warning
11:37:24 SQL>

My Observations:

I have noticed that if we use as of timestamp to select data from table the we are not getting consistent results below are some samples:

11:19:38 SQL> select timestamp_to_SCN (to_timestamp('14-MAR-14 11:19:13' , 'DD-MON-YY HH24:MI:SS') ) from dual;
TIMESTAMP_TO_SCN(TO_TIMESTAMP('14-MAR-1411:19:13','DD-MON-YYHH24:MI:SS'))
-------------------------------------------------------------------------
132437487
11:20:06 SQL> select * from status as of SCN 132437487;
NO TYPE
---------- ----------
2 Error
11:20:22 SQL> select * from status as of timestamp(to_timestamp('14-MAR-14 11:19:13' , 'DD-MON-YY HH24:MI:SS'));
NO TYPE
---------- ----------
1 working
11:20:26 SQL>

11:21:10 SQL>

11:21:10 SQL> select * from status as of SCN 132437561;
NO TYPE
---------- ----------
2 Warning
11:21:16 SQL> select * from status as of timestamp(to_timestamp('14-MAR-14 11:21:08' , 'DD-MON-YY HH24:MI:SS'));
NO TYPE
---------- ----------
1 working
11:21:28 SQL>

Monday, March 3, 2014

oracle dataguard borker setup and dataguard switchover

Oracle Setup Dataguard Broker

Our setup:
Primary site:
Database: RAC
Nodes: rac1, rac2

Standby site:
Database: RAC_STBY
Nodes: racdr1

Setup listener on primary and standby
add the below entry in listener.ora under grid listener
Primary Node1:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL.localdomain)
(SID_NAME = RAC1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)

Primary Node2:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL.localdomain)
(SID_NAME = RAC2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)

Standby:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC_STBY_DGMGRL.localdomain)
(SID_NAME = RAC_STBY1)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
)

Reload listener on primary and standby

lsnrctl reload

Setup the tnsnames.ora on primary and standby site, please note that we will use host vip as host rather than using scan

Primary node rac1 tnsnames.ora entry:

[oracle@rac1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.localdomain)
)
)
RAC_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STBY.localdomain)
)
)
RAC_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_DGMGRL.localdomain)
(INSTANCE_NAME=RAC1)
)
)
RAC_STBY_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STBY_DGMGRL.localdomain)
(INSTANCE_NAME=RAC_STBY1)
)
)

RCAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rcat1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RCAT1.localdomain)
)
)
[oracle@rac1 admin]$

Node rac2 tnsnames.ora entry:

[oracle@rac2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
[oracle@rac2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.localdomain)
)
)
RAC_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STBY.localdomain)
)
)
RAC_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_DGMGRL.localdomain)
(INSTANCE_NAME=RAC2)
)
)
RAC_STBY_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STBY_DGMGRL.localdomain)
(INSTANCE_NAME=RAC_STBY1)
)
)
RCAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rcat1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RCAT1.localdomain)
)
)

[oracle@rac2 admin]$

Standby site tnsnames.ora node racdr1:

[oracle@racdr1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.localdomain)
)
)
RAC_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STBY.localdomain)
)
)
RAC_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_DGMGRL.localdomain)
(INSTANCE_NAME=RAC1)
)
)
RAC_STBY_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racdr1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_STBY_DGMGRL.localdomain)
(INSTANCE_NAME=RAC_STBY1)
)
)

RCAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rcat1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RCAT1.localdomain)
)
)

[oracle@racdr1 admin]$

After all these setups try tnsping from each node using each tns entry like below. Make sure that it is working from all nodes (Primary and Standby)

tnsping RAC
tnsping RAC_STBY
tnsping RAC_DGMGRL
tnsping RAC_STBY_DGMGRL

Set the parameter on Primary

SQL> alter system set dg_broker_config_file1='+DATA/dr1rac.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='+DATA/dr2rac.dat' scope=both sid='*';
System altered.
SQL>
SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*';
System altered.
SQL>
SQL> show parameter dg
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string +DATA/dr1rac.dat
dg_broker_config_file2 string +DATA/dr2rac.dat
dg_broker_start boolean TRUE
SQL>

On standby set the parameters as:

SQL> alter system set dg_broker_config_file1='+DATA/dr1RAC_STBY.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='+DATA/dr2RAC_STBY.dat' scope=both sid='*';
System altered.
SQL>
SQL> alter system set dg_broker_start=TRUE scope=BOTH SID='*';
System altered.
SQL>
SQL> show parameter dg
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string +DATA/dr1RAC_STBY.dat
dg_broker_config_file2 string +DATA/dr2RAC_STBY.dat
dg_broker_start boolean TRUE
SQL>

[root@rac1 ~]# . ./db_env
[root@rac1 ~]# dgmgrl sys/******@rac
DGMGRL for Linux: Version 11.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>

Create the broker configuration

DGMGRL> create configuration RACDG as primary database is 'RAC' connect identifier is 'RAC';
Configuration "racdg" created with primary database "RAC"
DGMGRL>
DGMGRL> add database 'RAC_STBY' as connect identifier is 'RAC_STBY' maintained as physical;
Database "RAC_STBY" added
DGMGRL>
DGMGRL> enable configuration
Enabled.
DGMGRL>
DGMGRL> show configuration
Configuration - racdg
Protection Mode: MaxPerformance
Databases:
RAC - Primary database
RAC_STBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database 'RAC'
Database - RAC
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
RAC1
RAC2
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show database 'RAC_STBY'
Database - RAC_STBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
RAC_STBY1
Database Status:
SUCCESS
DGMGRL>
DGMGRL>

And the dataguard broker setup is done.

Switchover to standby database:
Make sure you are connected to standby database using dgmgrl and then do the switchover

[oracle@racdr1 ~]$ dgmgrl sys/********@RAC_STBY
DGMGRL for Linux: Version 11.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>
DGMGRL> switchover to 'RAC_STBY'
Performing switchover NOW, please wait...
New primary database "RAC_STBY" is opening...
Operation requires shutdown of instance "RAC1" on database "RAC"
Shutting down instance "RAC1"...
ORACLE instance shut down.
Operation requires startup of instance "RAC1" on database "RAC"
Starting instance "RAC1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "RAC_STBY"
DGMGRL>
DGMGRL> show configuration
Configuration - racdg
Protection Mode: MaxPerformance
Databases:
RAC_STBY - Primary database
RAC - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>

Logical Thinking

Pages