Oracle 11g Database New Features: Data Guard Enhancements

Data Guard is used to protect your data and insure its availability. 11g Increases ROI from standby systems and enhance manageability with new feature like the following

Active Data Guard
Snapshot standby

Here are some of the new features available with 11g Data Guard by category:

1) Data Protection

• Advanced Compression
• Lost-write protection
• Fast-Start Failover

2) High Availability

• Faster Redo Apply
• Faster failover & switchover
• Automatic Failover using ASYNC

Active Data Guard

Oracle Active Data Guard 11g – a new Database Option enables read-only access to a physical standby database while Redo Apply is active tt is now possible to query a physical standby database while Redo Apply is active. This new capability increases your return on investment in Data Guard technology because a physical standby database can now be used to offload queries from the primary database in addition to providing data protection. (We can do same with logical standby , what is different here , well redo apply method is faster in physical stand by ie redo apply method is different in physical standby)

• Stop log apply

sql> alter database recover managed standby database cancel;

• Open database for read-only access

sql> alter database open

• Once database open start redo apply

sql> alter database recover managed standby database
using current logfile disconnect from session;


Snapshot Standby

This enhancement is good example of ROI. This feature allow us to better utilization of standby. This feature Truly leverages DR hardware for multiple purposes.For example convert physical database to snapshot standby database do testing/ application patching etc. After testing convert back snapshot database back to physical standby.

• -- Convert physical database to snapshot

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

• --
• -- Do testing on database
• --

• -- Convert snapshot Database to physical database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

This Similar to storage snapshot , but it useage same space , not additional space is needed ( I know this possibly can achieve in 10g R2 too , using flashback feature , But for sure it is not easy as it is in 11g )


Redo compression

To transport redo data in compressed form to destination use COMPRESSION attribute.
By default compression is disabled.

• Alter system set log_archive_dest_1 = 'SERVICE=standby1 compression=ENABLE';
  
  
• select dest_name , compression from v$archive_dest;
  
  SQL>
  DEST_NAME COMPRES
  --------------------------------- -------
  LOG_ARCHIVE_DEST_1 DISABLE
  LOG_ARCHIVE_DEST_2 ENABLE
  LOG_ARCHIVE_DEST_3 DISABLE
  LOG_ARCHIVE_DEST_4 DISABLE
  LOG_ARCHIVE_DEST_5 DISABLE
  LOG_ARCHIVE_DEST_6 DISABLE
  LOG_ARCHIVE_DEST_7 DISABLE
  LOG_ARCHIVE_DEST_8 DISABLE
  LOG_ARCHIVE_DEST_9 DISABLE
  LOG_ARCHIVE_DEST_10 DISABLE
  
  10 rows selected.
  

Data Protection

New Initialization parameter db_lost_write_protect Introduced. This parameter Compare versions of blocks on the standby with that in the incoming redo stream
Version discrepancy implies lost write on either primary or standby database

db_lost_write_protect

Managibilty Enhancement

SYS user and password files no longer required for redo transmission authentication. Non-SYS user can be specified through the parameter, This user must have the SYSOPER privileges, Requires password for this user to be the same at primary and all standbys
Upon SYSDBA / SYSOPER changes, password file must be copied from the primary to all physical standby databases

redo_transport_user

Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration (- Since 11g Linux installer only available, so not able to test this feature)

Fast-Start Failover

Immediate automatic failover for user-configurable health conditions



DGMGRL> show FAST_START FAILOVER
DGMGRL> ENABLE FAST_START FAILOVER [CONDITION ];

Condition examples:

• Datafile Offline
• Corrupted Controlfile
• Corrupted Dictionary
• Inaccessible Logfile
• Stuck Archiver
• Any explicit ORA-xyz error

Application can also request for failover using package DBMS_DG

DBMS_DG.INITIATE_FS_FAILOVER

Oracle doesn't allow replacing ocr disk or adding the mirror copy if the new disk size is less than the existing ocr disk size

We are in the process of moving our OCR to a new storage array. I want to replace an existing OCR location and also trying to add mirror copy of OCR. We are currently using 2gb raw devices for OCR and voting disk. Our Oracle Consultant told to we only need 1gb raw devices for both our OCR and votes disks.

I have got the following errors:

for OCR Mirror copy: PROT-22:Storage is too small
for replace OCR : PROT-16: Internal Error

As usual, we search in the ML and found couple of notes for the same errors (317628.1 & 444757.1). Well, the ML Note : 317628.1 said the following:

" fails with "PROT-22: Storage too small" error. The problem is due to an Oracle bug where this operation requires the OCR mirror partition/file to be larger than the original by up to 128MB. The bug has been fixed in the 10.2.0.2 patchset."

The note says that this bug has been fixed in 10.2.0.2 patch, we are on 10.2.0.3 patch and the bug still persists.

Oracle RAC Linux Network Configuration

Perform the following network configuration on all nodes in the cluster!

Introduction to Network Settings

During the Linux O/S install you already configured the IP address and host name for each of the nodes. You now need to configure the /etc/hosts file as well as adjust several of the network settings for the interconnect. I also include instructions for enabling Telnet and FTP services.

Each node should have one static IP address for the public network and one static IP address for the private cluster interconnect. The private interconnect should only be used by Oracle to transfer Cluster Manager and Cache Fusion related data. Although it is possible to use the public network for the interconnect, this is not recommended as it may cause degraded database performance (reducing the amount of bandwidth for Cache Fusion and Cluster Manager traffic). For a production RAC implementation, the interconnect should be at least gigabit or more and only be used by Oracle.

Configuring Public and Private Network

In our two-node example, you need to configure the network on both nodes for access to the public network as well as their private interconnect.

The easiest way to configure network settings in RHEL4 is with the Network Configuration program. This application can be started from the command-line as the root user account as follows:

# su -
# /usr/bin/system-config-network &

Do not use DHCP naming for the public IP address or the interconnects; you need static IP addresses!

Using the Network Configuration application, you need to configure both NIC devices as well as the /etc/hosts file. Both of these tasks can be completed using the Network Configuration GUI. Notice that the /etc/hosts settings are the same for both nodes.

Our example configuration will use the following settings:

Server 1 (linux1)
Device	IP Address	Subnet	Purpose
eth0	192.168.1.100	255.255.255.0	Connects linux1 to the public network
eth1	192.168.2.100	255.255.255.0	Connects linux1 (interconnect) to linux2 (int-linux2)
/etc/hosts
127.0.0.1 localhost loopback # Public Network - (eth0) 192.168.1.100 linux1 192.168.1.101 linux2 # Private Interconnect - (eth1) 192.168.2.100 int-linux1 192.168.2.101 int-linux2 # Public Virtual IP (VIP) addresses for - (eth0) 192.168.1.200 vip-linux1 192.168.1.201 vip-linux2

Server 2 (linux2)
Device	IP Address	Subnet	Purpose
eth0	192.168.1.101	255.255.255.0	Connects linux2 to the public network
eth1	192.168.2.101	255.255.255.0	Connects linux2 (interconnect) to linux1 (int-linux1)
/etc/hosts
127.0.0.1 localhost loopback # Public Network - (eth0) 192.168.1.100 linux1 192.168.1.101 linux2 # Private Interconnect - (eth1) 192.168.2.100 int-linux1 192.168.2.101 int-linux2 # Public Virtual IP (VIP) addresses for - (eth0) 192.168.1.200 vip-linux1 192.168.1.201 vip-linux2

Note that the virtual IP addresses only need to be defined in the /etc/hosts file (or your DNS) for both nodes. The public virtual IP addresses will be configured automatically by Oracle when you run the Oracle Universal Installer, which starts Oracle's Virtual Internet Protocol Configuration Assistant (VIPCA). All virtual IP addresses will be activated when the srvctl start nodeapps -n command is run. This is the Host Name/IP Address that will be configured in the client(s) tnsnames.ora file.

Flush

Today I was asked to flush the database cache on one
of our test databases...

The proper command is:

ALTER SYSTEM FLUSH SHARED_POOL;

The command will clear out all SQL statements that are
located in the
Shared Pool Area. Oracle tracks of all SQL statements
that a user executes andstores parsed statements in
memory so that if a SQL statement has already been
executed then Oracle will not re-parse the statement.

There is an exception to this rule if the shared pool area
is not big enough, then the least recently used statements
(except for pinned packages) will be axed from memory.

By flushing the shared pool, all SQL statements are
removed from memory.

Is It Recommended To Apply Patch Bundles Released For 10.2.0.4?

I am in the process of upgrading a 9.2.0.7 database to 10.2.0.4... So are there any patches that should be installed on top of the regular patch set 10.2.0.4. This way I have the latest and greatest going forward with 10g. MetaLink Note 743554.1 answers that question...

MetaLink Note

"Yes, It is recommended to apply the latest Patch Bundle release for 10.2.0.4 whether or not you are hitting the Bug fixes provided in the bundle patches. Hence this has been included in Metalink 10.2.0.4 recommended patches list.
Go ahead and apply Patch 8362683 (Patch Bundle # 4) which is the latest Bundle patch released for 10.2.0.4 Database."

My Installed Products are not showing up in Oracle Universal Installer (OUI)

The last few weeks I have been in Real Application Cluster (RAC) manufacturing mode... Four environments with two nodes each. Besides the normal Cluster Ready Services (CRS) would not install without fight... My manufacturing process was going without a hitch. With 3 of the 4 environments completed I ran into an issue where I was unable to install the 10.2.0.4 patch set to my 10.2.0.1 CRS install. When I checked to see if what products had been installed I was shocked to see that my inventory was blank!

I verified that CRS was installed and running but was not sure why I could not see what products had been installed. I then proceeded to verify that I had in fact had proof in the oraInventory that I had indeed installed CRS. When I launched OUI I used the following command to get it pointed to the proper oraInventory location...

./runInstaller -invPtrLoc $ORACLE_BASE/oraInventory/oraInst.loc

When I launched OUI using that command I was able to view the products that were installed and saw my CRS install... So the obvious question is why is OUI not picking up the oraInst.loc... After researching I found out somehow during the creation of the oraInst.loc file the permissions in the /etc directory did not have read granted to it.

chmod 644 /etc/oraInst.loc

Once the command was ran, I was able to rerun the OUI and the oraInventory was able to pull up and read the oraInst.loc thust showing all products that were installed.

Revoke a ton of privileges all at once!

So.... I just found out that some user accounts were created some time ago and that they were given privileges they should not have.... No big deal just revoke these privs and everyone can go home happy.

However, the privileges granted to these users rank close to 2200 times! So in order to revoke I will have to run the following command 2200 times... I will be here all night.

revoke select on SCHEMA1.TABLE32 from USER1;

Well I have to be home in time for dinner and it is already 4:45PM. Here is a tip to get you in the car and on your way home.

1.) Find out how many privileges we are talking about. (as sysdba)

SQL>select count(*) from dba_tab_privs where grantee='USER1';

COUNT(*)
--------
2200

1 rows selected

2.) Now here comes the trick... Create your DML statement on the fly with this little gem.

SQL>select 'revoke select on '||''||owner||'.'||table_name||''||' from USER1;' from DBA_TAB_PRIVS where grantee='USER1';

This will generate 2200 hundred lines of revoke statements for you to script up and run while your packing your briefcase to go home!

How to delete a 10g Database Manually

Today I came across an issue with dbca.... Usually you can easily drop an oracle database using dbca. However today I was forced to manually to delete the database in question....

The following steps will help you to manually drop a database.

1.)Set the Oracle SID of the database which you interested to drop.

$export ORACLE_SID=yoursid

2.)Connect to SQL*Plus as SYSDBA privilege.

$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

3.)Mount the database in restricted mode.

SQL> startup RESTRICT mount

ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 92274728 bytes
Database Buffers 67108864 bytes
Redo Buffers 6369280 bytes
Database mounted.

4.)Drop the database.

SQL> drop database;
Database dropped.

Welcome to Oracle DBA Man...

Here I will be posting some of personal experiences as an Oracle DBA. A little bit of history... I have been working in the Information Technology field since 1992 and as a DBA since 1998. First started working with Informix and then switch over to Oracle in 2003. Some of the organizations I have worked with in the past include Dell Computer Corporation, Department of Defense and currently a telco provider located in Atlanta Georgia.