All 31 entries tagged Oracle-Availability

VIP in RAC

What’s SCAN IP used for?

Receive connection from client and pass on to local listeners

• Each SCAN VIP has SCAN Listener associated with each other and both works as a pair. One SCAN Listener is created corresponding to one SCAN VIP.

• Whenever a new connection request is made the SCAN listener listening on a SCAN IP address and the SCAN port is contracted on a client’s behalf. Because all services on the cluster (HS: even the nodes that have no scan listener running on) are registered with the SCAN listener, the SCAN listener replies with the address of the local listener as Node VIP address on the least-loaded node (Each scan listener keeps updated cluster load statistics) and connection is routed to that node.

What’s VIP used for ?

The local listener run on the VIP. If local node failed, the VIP is moved to surviving node, avoiding the long delay of TCP time out.

Command:

srvctl status vip -n rad1dev
srvctl config scan
srvctl config scan_listener

Very good article:

http://www.dbas-oracle.com/2013/08/SCAN-NAME-SCAN-VIP-SCAN-Listener-Node-VIP-Local-Listener-Remote-Listener-RAC.html

http://satya-racdba.blogspot.co.uk/2012/07/virtual-ip-vip-address-rac-oracle.html

• Virtual IP must be associated with the same interface name on every node that is a part of the cluster
• The Virtual IP address should not be in use at the time of the installation because this is a Virtual IP address that Oracle manages internally to the RAC processes.
• This virtual IP address does not require a separate NIC. The VIPs should be registered in the DNS.
• Virtual IP (VIP) is for fast connection establishment in failover dictation

Hongfeng Sun : 12 Dec 2013 10:40 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

Switchover to logical standby

This morning we did a switchover between our primary database and one of the two logical standby databases.

The switchover went smoonthly. When the data began to be replicated from primary, however, the standby database suddenly did successive log switch at very high frequency. This is a bit puzzling since the primary database was not too busy.

Loads of waiting events from “SYS” user:

LogMiner builder: DDL                    oracle@si-sbr-db (MS01)  
LogMiner reader: buffer                  oracle@si-sbr-db (MS00) 
Streams apply: waiting for dependency    oracle@si-sbr-db (AS05)                     
Streams apply: waiting for dependency    oracle@si-sbr-db (AS04)   

The frequent updating SQL was:

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 
where obj#=:1

We started to replicate a big log table a couple of days ago. That table was previous skipped in log apply. The old logical standby coped the change very well. Did this change cause problem on the new standby?
I increased the cache size of hibernate sequence to 10,000 from default value 20.

alter sequence hitlog.hibernate_sequence cache 10000

The log switch immediately stopped. I was surprised how effective the SQL was.

ORA-16282
At developer’s request, I did switchover again. Went smoothly. Then I was asked to switchover again ( for some reason of course), I encountered an error on the current standby database:

SYS@standby SQL>ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; 
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16282: operation not permitted during rolling upgrade

After I faffed around on google, then I recalled I forgot to startup the log apply on the current standby.
Well, the “PREPARE TO SWITCHOVER TO PRIMARY” succeeded after I started the log apply.

Hongfeng Sun : 06 Feb 2012 12:36 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

Warning messages after failover

I failovered to standby database. The following message kept on loged in alert log.

RFS[303]: Assigned to RFS process 18298
RFS[303]: Database mount ID mismatch [0x5f0edd7b:0x5788f47c] (1594809723:1468593276)
RFS[303]: Destination database ID mismatch [0x5bb6deb1:0x5775211a] (1538711217:1467293978)
RFS[303]: Not using real application clusters

I unset fal_client, fal_server, and log_archive_config the message wont go away.
Suddenly realized that the old primary is still sending archive log to this new standby.

SQL>alter system reset log_archive_dest_2;  (this is a 10g db)
ERROR at line 1:ORA-00905: missing keyword

SQL>alter system set log_archive_dest_state_2=defer;
System altered.

That stopped the message.

Hongfeng Sun : 18 Jan 2012 15:54 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

Two unexpected thing happened when creating logical standby

1. When run the command
alter database recover to logical standby pewsos

It just hung there and the log reported “Media Recovery Waiting for thread 1 sequence xxx”

alter system set log_archive_dest_2=’service=pewsos LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=pewsos’;

I have to manual copy the log file over and completed the command.

It hung again. I get rid of ” LGWR ASYNC” and work properly

2. It failed to restart , reporting:
ORA-16181: SGA specified for Logical Standby is too large Cause: MAX_SGA is larger than the larger of initialization parameters

It turned out to be the “MAX_SGA” parameter in logical standby settings is too big.

Hongfeng Sun : 25 Feb 2011 16:42 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

11g physical standby

Follow-up to Physical standby vs logical standby from Oracle/Java/Others

Rolling upgrade:
You can do that.

Switchover:

The old primary db need to be shutdown.
You need to let your application use the standby before start. Can you open your physical standby in read-only mode

1. On primary:
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;

2. On primary:
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

3. Shutdown and then mount former primary
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

4 Verify that the switchover target is ready to be switched to the primary role.
SELECT SWITCHOVER_STATUS FROM V$DATABASE;

5. Switch the target physical standby database role to the primary role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

6 Open the new primary database.
SQL> ALTER DATABASE OPEN;

7 Start Redo Apply on the new physical standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Hongfeng Sun : 30 Sep 2010 10:48 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

oracle listener config

I puppetized Oracle listener.ora, then suddenly I cannot connect to Oracle

sqlplus system/xxx@db_service

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Solaris-AMD64 Error: 2: No such file or directory

I checked the configuration, everything seems fine.
ORACLE_HOME=/package/oracle/product/10.2.0
ORACLE_SID=argon

After some fruitless effort, I notice the value $oracle_home in of /var/opt/oracle/oratab is /usr/local/oracle. /usr/local/oracle is the symlink of /package/oracle. I changed the value of /var/opt/oracle/oratab to /package/oracle and reset the environment variable ORACLE_HOME to /package/oracle. Restarted the database and I can now connect to database.

Hongfeng Sun : 02 Aug 2010 15:23 |  Tags: Oracle Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

11g logical standby database creation

In 10g, you manually create the password file.
In 11g, you have to copy password from primary. Otherwise, the following error will be reported:

*** 2010-07-08 14:46:17.196 4132 krsh.c
Error 1031 received logging on to the standby
*** 2010-07-08 14:46:17.196 869 krsu.c
Error 1031 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'xxx'
ORA-01031: insufficient privileges
*** 2010-07-08 14:46:17.197 4132 krsh.c
Error 1031 for archive log file 1 to 'xxxx'
*** 2010-07-08 14:46:17.197 2747 krsi.c
krsi_dst_fail: dest:2 err:1031 force:0 blast:1
ORA-01031: insufficient privileges
Destination is specified with ASYNC=61440

You also need to change the name of the password file.

So the command will be:

scp oracle@primary:/usr/orahome/dbs/orapwPrimarySid /usr/orahome/dbs/orapwStandbySid

The database do not need to be restarted.

Hongfeng Sun : 08 Jul 2010 15:11 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

11g new features

Some new features might help developer:

1. Off load read to active data guard , set standby_max_data_delay = none. It throw exception if standby is not in sync with primary. SB can identify the exception and read data again from primary.

2. Flashback data archive can keep history data for weeks, months. Helpful audit_trail or editing history

3. Direct NFS

4. Make a table read only
5. Add a NOT NULL column with a default value much faster.

For DBA:

invisible index: Test drop index by making it invisible

bdump, cdump and udump is depreciated, but adump is kept.

color sql: let AWR caputre it even it not in top sql

rows are inserted uncompressed and in the routine way

alter index in_tab_01 rebuild online; No blocking at all

Hongfeng Sun : 01 Dec 2009 20:43 |  Tags: Oracle-Availability |  Comments (2) |  Close comments |  Report a problem

DBMS_LOGSTDBY.INSTANTIATE and Job already exists error

When you run DBMS_LOGSTDBY.INSTANTIATE_TABLE, it actually create a table SYS_IMPORT_TABLE_nn, (I run as sys. If you run as another user, the table name might be different.).

After the job run successfully, Oracle does not delete the table.

Seems Oracle only allowing 100 such tables (i.e., nn between 0 and 99). If you run the procedure more than 100 times, you will receive an error ORA-31634: job already exists.

Quite a silly omission from Oracle.

You can use : select * from dba_datapump_jobs order by job_name to verify.

The solution is quite simple, just drop the table SYS_IMPORT_TABLE_nn. Before dropping it, make sure the job associated with the table is not running. The status is “NOt Running” in dba_datapump_jobs.

Hongfeng Sun : 28 May 2009 16:35 |  Tags: Oracle-Availability |  Comments (0) |  Close comments |  Report a problem

logical standby failed to re–start

It only give the following error messages.

krvxerpt: Errors detected in process 52, role LOGICAL STANDBY COORDINATOR.
Tue Mar 17 10:00:05 2009
krvsqn2s: unhandled failure 604

This happened when you set logical standby to skip some schemas.

The workaround is documented in metalink 748208.1

set echo on 
set pagesize 100 
spool workaround.log 
select * from system.logstdby$skip; 
select distinct nvl(esc, 'NULL') from system.logstdby$skip; 
select * from system.logstdby$skip where esc is null; 
update system.logstdby$skip 
set esc = '\' 
where esc is NULL; 

-- Following should return no rows (due to update above) 
select * from system.logstdby$skip where esc is null; 
-- should no longer see any NULL in output 
select distinct nvl(esc, 'NULL') from system.logstdby$skip; 
-- Capture a snapshot of the final results 
select * from system.logstdby$skip; 

-- commit changes 
commit;

Hongfeng Sun : 17 Mar 2009 10:37 |  Tags: Oracle-Availability |  Comments (5) |  Close comments |  Report a problem