ORA-03297: file contains used data beyond requested RESIZE value

You can set your datafiles size up to hwm_bytes columns or you can resize your hwm through alter table move + rebuild index or export and import. Error : SQL> select bytes/1024 from dba_data_files where file_id = 7; bytes/1024 ----------       7168 SQL> select sum(bytes/1024) from dba_segments where tablespace_name = 'USERS'; sum(bytes/1024) ---------------            5440 SQL> alter database  datafile '/u02/data/db_1/db/ users 01.dbf' resize 5500k; alter database * error at line 1: ORA-03297: file contains used data beyond requested resize value Solution: You can resize your datafiles up to the hwm_bytes using the below output select  a.file_name,    a.bytes file_size_in_bytes, (c.block_id+(c.blocks-1)) * &block_size hwm_bytes, a.bytes - ((c.block_id+(c.blocks-1)) * &block_size) saving from dba_data_files a, (select file_id,max(block_id) maximum from dba_extents group by file_id) b, dba_extents c where a.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum and c.tablespace_name = ' USERS ' SQL> / enter value for _block_size: 8192 old   4:    (c.block_id+(c.blocks-1)) * &block_size hwm_bytes, new   4:    (c.block_id+(c.blocks-1)) * 8192 hwm_bytes, enter value for _block_size: 8192 old   5:    a.bytes - ((c.block_id+(c.blocks-1)) * &block_size) saving new   5:    a.bytes - ((c.block_id+(c.blocks-1)) * 8192) saving file_name            file_size_in_bytes  hwm_bytes     saving -------------------------------- ------------------ ---------- ---------- /u02/data/db_1/db/ users 01.dbf            7340032              6356992                        983040 SQL> alter database datafile '/u02/data/db_1/db/ users 01.dbf' resize 6356992; database altered.

Oracle RAC vs Dataguard

RAC Overview:

         

RAC means that two or more computers are sharing a common set of disk.  All of the computers have read write access to the data in the single (one) database. If one computer (node) fails in the cluster - work continues as before - the database never goes down

Good: RAC is a good solution for hardware failures, system failures, and software failures.

Good: Sharing resources across servers.

Good: RAC addresses system failures such as s node failures, or instance crashes.

Good: load balancing: you have multiple instances running on the same database and any new connection is routed to the instance with less work load in the available instances.

Disadvantage: RAC is very expensive – you need many servers, fiber channel infrastructure, eventually Storage Area Network. Of course this cost money.

Data Guard Overview:

Data guard is a configuration, which has at least one standby database of the primary database. The primary database can have one or more standby databases.

Good:  This enables recovery from site disasters (fire, flood, explosion, whatever) oracle data corruptions.

Good:  Can use the data guard site for read-only reporting purposes if we required.

Good:  Data Guard provides data protection.

Disadvantage:  The Data Guard option is only available in the enterprise edition of oracle software.

Few Difference between Oracle RAC and Data Guard:

•           RAC has one database and several instances associates with it, but data guard has several databases (one primary and others standby databases).

•       RAC is the recommended solution for instance, software and hardware level failures. Data guard is the recommended solution for the SITE failures.

•        RAC must have a shared storage, which can be accessed from all the nodes of the system, but in data guard there is no shared storage, which is common for all the sites.

•         RAC is Active-Active solution, expensive cost wise:: Data guard is Active-Passive , less expensive compare than RAC.

•      RAC nodes are placed within the same premises (physically close together): Data Guard site can be physically remote (different sites at unlimited distances from each other).

•       RAC licensing is more expensive than Data Guard licensing: Data Guard is not an extra cost option, but you need to pay for a full oracle license at the standby node.

Implementing UDEV rules for setting disk permission on ASM disks

 Error Message :

Connection Failed!!!

ORA-00604: error occurred at recursive SQL level 1

ORA-01115: IO error reading block from file  (block # )

ORA-01110: data file 1: '+DATA01/Prd605/datafile/system.259.817302197'

ORA-15081: failed to submit an I/O operation to a disk

The oracle database Prd605 is down, My 11gr2 database on RHEL 6.3. When we checked the alert log file of the database, we came to know that the asm raw file /dev/dm-16 is not accessible.Also the permissions of the /dev/dm-* are changed to root:disk instead of oracle:oinstall.

Root Cause:

ASM could not communicate to the Database due to all existing ASM disks went to read only mode and permissions are changed to ROOT after multipath reload.

To fix this issue, we have defined necessary udev rules to preserve ownership permission while reloading multipath service

Solution:

1.   Create a file with the UDEV rules for raw device permission setting should be placed into rule files under the directory/etc/udev/rules.d/

2.   The file should contain the default system rules contained in the 

50-udev-permissions.rules file

3.   Add the below content in the above file (Note: Change the {DM_NAME}==”____“ according to the disk name)

ENV{DM_NAME}=="ASM?*", OWNER:="oracle", GROUP:="oinstall", MODE:="660"

4.   start the start_udev service

[root@linux1]# start_udev

Starting udev:      [  OK  ]

[root@linux1]#

A few words on Oracle Database Product Family

Oracle features and options are frequently changing, so please refer MOS note for the latest Oracle features and options packages.

Oracle Database is available in five editions, each suitable for different development and deployment scenarios. Oracle also offers several database options, packs, and other products that enhance the capabilities of Oracle Database for specific application purposes.

Oracle RDBMS editions:

• Oracle Enterprise Edition (EE) – Full featured edition of Oracle RDBMS which allows all core features along with the option to purchase add-on features (like Partitioning) and Management Packs (like the Diagnostics Pack).

• Oracle Standard Edition (SE) – A damped down of edition of Oracle which can run on a server with a maximum of four CPU sockets. It cannot take advantage of add-ons or Management Packs. It does, however, include Oracle RAC as long as all nodes combined have no more than 4 sockets.

• Oracle Standard Edition One (SE1) – A further reduced edition which shares the same features with SE but is limited to 2 CPU Sockets with no RAC option.

• Oracle Express Edition (XE) – A free use version of Oracle which can use a single CPU (though it can be installed on a server with as many CPUs as you want), 1GB RAM, and 11GB data.

• Oracle Personal Edition (PE) – Single User, Single Machine development/deployment license which can use any SE1, SE, or EE feature with the exception of RAC and Management Packs.

Database version history

The major Oracle versions, with their latest patch-sets are:

• Oracle v2 : 2.3
• Oracle v3 : 3.1.3
• Oracle v4 : 4.1.4.0-4.1.4.4
• Oracle v5 : 5.0.22, 5.1.17, 5.1.22
• Oracle v6 : 6.0.17-6.0.36 (no OPS code), 6.0.37 (with OPS)
• Oracle7: 7.0.12–7.3.4
• Oracle 8: 8.0.3 - 8.0.6
• Oracle 8i: 8.1.5.0 - 8.1.7.4
• Oracle 9i (Release 1): 9.0.1.0 - 9.0.1.4
• Oracle 9i (Release 2): 9.2.0.1 - 9.2.0.8
• Oracle 10g (Release 1): 10.1.0.2 - 10.1.0.5
• Oracle 10g (Release 2): 10.2.0.1 - 10.2.0.5
• Oracle 11g (Release 1): 11.1.0.6 - 11.1.0.7
• Oracle 11g (Release 2): 11.2.0.1 - 11.2.0.4
• Oracle Database 12c (Release 1): 12.1.0.1.0 - 12.1.0.2.0. (Recent release)

De-support dates

• Oracle 9iR2 was desupported on 31 July 2007. However, customers on Oracle 9.2.0.8 received free Extended Support until July 31, 2008.
• Oracle 10g Release 1 was desupported on 31 January 2009 and extended support ended on 31 January 2012.
• Oracle 10g Release 2 was desupported on 31 July 2010 and extended support ended on 31 July 2013.
• Oracle 11g Release 1 was desupported on 31 August 2012 and extended support will end on 31 August 2015.
• Currently, Oracle 11g Release 2 is fully supported until 31 January 2015 and Oracle database 12c is fully supported until 31-Jul-2018.