ANAND BLOG

Friday, 3 May 2013

Setup DNS for SCAN used in Oracle RAC 11gR2

Setup DNS for SCAN used in Oracle RAC 11gR2

Create for example a VM machine named mydns and install following:

Need RPMs

[root@mydns ~]# rpm -qa | grep bind

ypbind-1.19-12.el5_6.1

bind-9.3.6-16.P1.el5

bind-utils-9.3.6-16.P1.el5

bind-libs-9.3.6-16.P1.el5

/etc/named.conf

# minimal DNS conf file for RAC 11g

options {

//i Forward any name this DNS can't resolve to my router.

forwarders { 192.168.80.2; };

// Directory where named will look for zone files.

directory "/var/named";

};

# Forward Zone

zone "mydom.com" IN {

type master;

file "mydom.com.zone";

allow-update { none; };

};

# Reverse Zone

zone "80.168.192.in-addr.arpa" IN {

type master;

file "80.168.192.zone";

allow-update { none; };

};

Overview

Server	DNS	RAC node 1	RAC node 2
Public Name	mydns.mydom.com	rac1.mydom.com	rac2.mydom.com
Private Name	-	rac1-priv.mydom.com	rac2-priv.mydom.com
VIP Name	-	rac1-vip.mydom.com	rac2-vip.mydom.com
Public IP – eth0	192.168.80.140	192.168.80.151	192.168.80.152
VIP IP – eth0:1	-	192.168.80.171	192.168.80.172
Private IP – eth1	-	192.168.136.151	192.168.136.152

SCAN Adresse	FQN	Shortname
192.168.80.201	rac-scan.mydom.com	rac-scan
192.168.80.202	rac-scan.mydom.com	rac-scan
192.168.80.203	rac-scan.mydom.com	rac-scan

Zone file /etc/named/mydom.com.zone

$ORIGIN mydom.com.

$TTL 1D ; time-to-live - (1 day)

@ IN SOA mydns.mydom. master.mydom.com. (

201011021 ; serial number - (yyyymmdd+s)

1d ; refresh - (1 day)

1h ; retry - (1 hour)

1w ; expire - (1 week)

60 ; minimum - (1 minute)

)

;

@ NS mydns ; mydns.mydom.com is the name server

localhost A 127.0.0.1

; RAC nodes

rac1 A 192.168.80.151

rac2 A 192.168.80.152

rac1-vip A 192.168.80.171

rac2-vip A 192.168.80.172

rac1-priv A 192.168.136.151

rac2-priv A 192.168.136.152

; DNS

mydns A 192.168.80.140

; SCAN - Single Client Access Name

rac-scan A 192.168.80.201

rac-scan A 192.168.80.202

rac-scan A 192.168.80.203

Reverse zone file /etc/named/80.168.192.zone

$ORIGIN 80.168.192.in-addr.arpa.

$TTL 1D ; time-to-live - (1 day)

@ IN SOA mydns.mydom. master.mydom.com. (

201011021 ; serial number - (yyyymmdd+s)

1d ; refresh - (1 day)

1h ; retry - (1 hour)

1w ; expire - (1 week)

60 ; minimum - (1 minute)

)

;

@ NS mydns ; mydns.mydom.com is the name server

; RAC nodes

151 PTR rac1.mydom.com.

152 PTR rac2.mydom.com.

; RAC-VIP nodes

171 PTR rac1-vip.mydom.com.

172 PTR rac1-vip.mydom.com.

; DNS server

40 PTR mydns.mydom.com.

; SCAN - Single Client Access Name

201 PTR rac-scan.mydom.com.

202 PTR rac-scan.mydom.com.

203 PTR rac-scan.mydom.com.

/etc/resolv.conf (in DNS machine)

[root@mydns ~]# cat /etc/resolv.conf

nameserver 127.0.0.1

search mydom.com

Check config file

[root@mydns ~]# named-checkconf /etc/named.conf

[root@mydns ~]# named-checkzone mydom.com /var/named/mydom.com.zone

zone mydom.com/IN: loaded serial 201011021

[root@mydns ~]# named-checkzone 80.168.192.in-addr.arpa /var/named/80.168.192.zone

zone 80.168.192.in-addr.arpa/IN: loaded serial 201011021

[root@mydns ~]#

Run service named

[root@mydns ~]# service named start

Starting named: [ OK ]

[root@mydns ~]#

/etc/resolv.conf (in a RAC Node)

[root@rac1 ~]# cat /etc/resolv.conf

nameserver 192.168.80.140

search mydom.com

[root@rac1 ~]#

Test

[root@rac1 ~]# nslookup rac1

Server: 192.168.80.140

Address: 192.168.80.140#53

Name: rac1.mydom.com

Address: 192.168.80.151

[root@rac1 ~]# nslookup rac1-vip

Server: 192.168.80.140

Address: 192.168.80.140#53

Name: rac1-vip.mydom.com

Address: 192.168.80.171

[root@rac1 ~]# nslookup rac-scan

Server: 192.168.80.140

Address: 192.168.80.140#53

Name: rac-scan.mydom.com

Address: 192.168.80.201

Name: rac-scan.mydom.com

Address: 192.168.80.202

Name: rac-scan.mydom.com

Address: 192.168.80.203

[root@rac1 ~]# nslookup rac-scan

Server: 192.168.80.140

Address: 192.168.80.140#53

Name: rac-scan.mydom.com

Address: 192.168.80.203

Name: rac-scan.mydom.com

Address: 192.168.80.201

Name: rac-scan.mydom.com

Address: 192.168.80.202

Note: nslookup must return 3 IPs in a different order each time

[root@rac1 ~]# dig rac-scan.mydom.com

; <<>> DiG 9.3.6-P1-RedHat-9.3.6-16.P1.el5 <<>> rac-scan.mydom.com

;; global options: printcmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 10628

;; flags: qr aa rd ra; QUERY: 1, ANSWER: 3, AUTHORITY: 1, ADDITIONAL: 1

;; QUESTION SECTION:

;rac-scan.mydom.com. IN A

;; ANSWER SECTION:

rac-scan.mydom.com. 86400 IN A 192.168.80.202

rac-scan.mydom.com. 86400 IN A 192.168.80.203

rac-scan.mydom.com. 86400 IN A 192.168.80.201

;; AUTHORITY SECTION:

mydom.com. 86400 IN NS mydns.mydom.com.

;; ADDITIONAL SECTION:

mydns.mydom.com. 86400 IN A 192.168.80.140

;; Query time: 0 msec

;; SERVER: 192.168.80.140#53(192.168.80.140)

;; WHEN: Tue Apr 24 10:56:49 2012

;; MSG SIZE rcvd: 120

[root@rac1 ~]# dig -x 192.168.80.203

; <<>> DiG 9.3.6-P1-RedHat-9.3.6-16.P1.el5 <<>> -x 192.168.80.203

;; global options: printcmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 56995

;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 0

;; QUESTION SECTION:

;203.80.168.192.in-addr.arpa. IN PTR

;; ANSWER SECTION:

203.80.168.192.in-addr.arpa. 86400 IN PTR rac-scan.mydom.com.

;; AUTHORITY SECTION:

80.168.192.in-addr.arpa. 86400 IN NS mydns.80.168.192.in-addr.arpa.

;; Query time: 0 msec

;; SERVER: 192.168.80.140#53(192.168.80.140)

;; WHEN: Tue Apr 24 10:57:14 2012

;; MSG SIZE rcvd: 97

[root@rac1 ~]#

Link:
http://www.oracle.com/technetwork/products/clustering/overview/scan-129069.pdf

Monday, 4 March 2013

Pre-check For SWITCHOVER using DG Broker

PRE-CHECK FOR SWITCHOVER USING DG BROKER

1. Verify the primary database instance is open (READ/WRITE Mode) and the standby database instance is mounted.

SELECT NAME, OPEN_MODE FROM GV$DATABASE;

2. Verify there are no active users connected to the databases.

SET LINES 10000 pages 10000

SELECT SID, SCHEMANAME, OSUSER, MACHINE, STATUS FROM GV$SESSION WHERE USERNAME IS NOT NULL;

3) Check for any active jobs running

SELECT * FROM DBA_JOBS_RUNNING;

SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED
FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';

4) Check the primary db has standby redolog files

SELECT GROUP#, BYTES/1024, STATUS FROM GV$STANDBY_LOG;

5) Check standby database has tempfile and it should match the size of the temp file from primary db.

SELECT NAME, BYTES FROM V$TEMPFILE;

6) Switch the backup process to the new primary database, if the backup policy is from the primary db.

7) Check whether the following settings are available for redo transport services such as LogXptMode, NetTimeout, LogShipping, StandbyFileManagement, FastStartFailoverTarget, StandbyArchiveLocation & AlternateLocation., DelayMins in the standby & primary database.

DGMGRL> SHOW DATABASE VERBOSE 'PRIMARY_DB_UNIQUE_NAME';

DGMGRL> SHOW DATABASE VERBOSE 'STANDBY_DB_UNIQUE_NAME';

Check whether StaticConnectIdentifier is configured on ALL NODES, so you have run this command 4 times if you have 2 node RAC primary & standby database.

SHOW INSTANCE VERBOSE 'INSTANCE_NAME' ON DATABASE 'DB_NAME'

Check start options for the primary & standby database

srvctl config database -d ‘PRIMARY_DB_NAME’ –a

srvctl config database -d ‘STANDBY_DB_NAME’ –a

Check the DELAYMINS, if it has set for value. Reduce the DELAYMINS to ZERO and apply all the logs before starting switchover.

DGMGRL> SHOW DATABASE 'PRIMARY_DB_UNIQUE_NAME' DELAYMINS;
DGMGRL> EDIT DATABASE 'PRIMARY_DB_UNIQUE_NAME' SET PROPERTY 'DELAYMINS'='0';

8) Check the datafile status in the standby database.

SELECT DISTINCT STATUS FROM V$DATAFILE;

Note: The status should be in “ONLINE” & “SYSTEM” mode. If any file/files are in RECOVER status, then identify the reason.

Check the offline datafiles in the primary & standby database

SELECT DISTINCT STATUS FROM V$DATAFILE_HEADER WHERE STATUS <> 'ONLINE';

9) Perform a log switch from the primary DB and verify logs are applied on the standby database.

IF RAC

ALTER SYSTEM ARCHIVE LOG CURRENT;

NON – RAC

ALTER SYSTEM SWITCH LOGFILE;

10) Make sure things are fine with the database on both primary (All instances) and Standby database (All instances). Check alert log, trace files from all nodes to make sure the database running without any issues.

11) check the status of the DG Broker LISTENER status, it should be up and running and check the status of the REMOTE_LISTENER, LISTENER_NETWORKS, LOCAL_LISTENER

lsnrctl status <LSNR_NAME>

12) Check FAST START FAILOVER is enabled and check the preferred STANDBY database.

13) Once the switch over process completes, Oracle DG Broker will have the same DG protection mode & Network Transmission role for the NEW PRIMARY DB.

14) Check the following

The primary database is enabled and is in the TRANSPORT-ON state.

DGMGRL> show database 'PRIMARY_DB_UNIQUE_NAME';

Note: Check for “Intended State” and it should be “TRANSPORT-ON”

The target standby database is enabled and is in the APPLY-ON state.

DGMGRL> show database 'STANDBY_DB_UNIQUE_NAME';

Note: Check for “Intended State” and it should be “APPLY-ON”

15) Check the flashback database is enabled in the primary/standby database. If it is not enabled then enable the FLASHBACK database

SELECT FLASHBACK_ON FROM GV$DATABASE;

TIPS

“ORA-12514 during the switchover” - Check whether the StaticConnectIdentifier is set correctly

TO CHECK (NEED TO CHECK ON ALL THE INSTANCES)

SHOW INSTANCE VERBOSE 'INSTANCE_NAME' ON DATABASE 'DB_NAME'

TO CHANGE

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier='';

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.225)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=DG112I_PRM_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i1)(SERVER=DEDICATED)))';

ORA-01017 SYS Password problem in the Data Guard environment

Problem

While performing the switchover & failover using the DG broker. DG broker reported the below error message in the log file.

Error message

Connection to database BHU_A returns ORA-01017.

NSV0: Failed to connect to remote database BHU_A. Error is ORA-01017

NSV0: Failed to send message to site BHU_A. Error code is ORA-01017.

DMON: failed to forward op EDIT_RES_PROP to site BHU_A with error ORA-01017

DMON: Database BHU_A returned ORA-01017

Connection to database BHU_A returns ORA-01017.

Please check database BHU_A is using a remote password file

When have a two node RAC setup as a primary DB and two node RAC setup as standby database. We have enable data Guard broker configuration for this environment.

PROBLEM CAUSE

When we change the sys password in the primary database using the alter system command, it updates the password file in the ORACLE_HOME of the primary database, it doesn’t update password file in the standby database.

SQL> alter user sys identified by oracle12$;

SOLUTION è Changing SYS Password in the Data Guard environment

While changing the sys password in the Primary database. You have manually copy the password file to the standby database. If it is RAC environment, then make sure that you place the file in both the ORACLE_HOME.

In Oracle 11g, Oracle has come up with case-sensitive option. It means passwords are case-sensitive. It is dynamic parameter and it change modified easily. this function available from oracle 11gR1 onwards.

SQL> show parameter case_sensitive

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean TRUE

SQL> alter system set sec_case_sensitive_logon=FALSE scope=both sid='*';

System altered.

Cloning Existing Oracle home (RDBMS) using clone.pl or runInstaller

we have a server which runs two Oracle databases on 10203 release on Linux. We had a plan to upgrade only one database on server to 10205 and keep the other database in the same version (10203) on the server

I have document the process of clone, i hope this will be useful.

STEP#1

Before starting the upgrade, we have to clone the ORACLE_HOME for the existing database to continue running on the same version.

MY EXISTING ORACLE_HOME è /oracle/BHUVAN/10203

MY NEW ORACLE_HOME è /oracle/BHUVAN/10205

STEP#2

Before starting the copy process, we have to stop all databases, listeners(all oracle products) that are running from the source home.

Once all oracle process are stopped, we can perform the copy

$ cd /oracle/BHUVAN/10203
$ tar -cvf /tmp/source_1023.tar .

Since I am restoring in the same server, I didn’t move the tar file. If you are moving to a different server then you have use the binary option for transferring the tar file.

$ cd /oracle/BHUVAN/10205

$ tar -xvf /tmp/source_1023.tar

STEP#3

We are perform the clone using two methods

1) Clone.pl

2) Using runInstaller option.

I have performed through runInstaller, but I am specifying the example of cloning using the clone.pl. When you want use the clone.pl, we should have the Perl installed on our Local server. You can check by issuing

$ perl -v

When you perform the runInstaller method, we don’t require any display or any DB console. In end of the clone setup process, oracle will ask us to run the root.sh from the root user.

Note: we don’t need to do the relink using both options.

EX CLONE.PL METHOD

oracle[BHUVAN]> perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_HOME="/oracle/BHUVAN/10205" ORACLE_HOME_NAME="ORA_1205_HOME"

EX RUNINSTALLER METHOD

oracle[BHUVAN]> ./runInstaller -silent -clone -waitForCompletion "ORACLE_HOME=/oracle/BHUVAN/10205" "ORACLE_HOME_NAME=ORA_1205_HOME" -noConfig -nowait

Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-10-02_10-11-03AM. Please wait ...Oracle Universal Installer, Version 10.2.0.3.0 Production

You can find a log of this install session at:

/oracle/BHUVAN/oraInventory/logs/cloneActions2011-10-02_10-11-03AM.log

.................................................................................................... 100% Done.

Installation in progress (Sun Oct 02 10:11:34 BST 2011)

................................................................................. 81% Done.

Install successful

Linking in progress (Sun Oct 02 10:11:50 BST 2011)

Link successful

Setup in progress (Sun Oct 02 10:17:49 BST 2011)

Setup successful

End of install phases.(Sun Oct 02 10:17:57 BST 2011)

WARNING:

The following configuration scripts need to be executed as the "root" user.

#!/bin/sh

#Root script to run

/oracle/BHUVAN/10205/root.sh

To execute the configuration scripts:

1. Open a terminal window

2. Log in as "root"

3. Run the scripts

The cloning of CLONE_HOME was successful.

Please check '/oracle/BHUVAN/oraInventory/logs/cloneActions2011-10-02_10-11-03AM.log' for more details.

STEP#4 (it is optionally only)

Once we complete the process, we can run the ChangePerm.sh to set the proper permission.

$ORACLE_HOME/install/changePerm.sh

STEP#5

Once the Entire clone process completed, we should move the spfile or pfile, tnsnames.ora, listener.ora to the new ORACLE_HOME for the database.