Saturday, August 27, 2011

Maximum datafile size limits...

Max. Number of Data Files in 9i and 10g is limited to 65,536.

Max. Number of blocks in a data file is:
4,194,304 (4 million) in 9i and increased to
4,294,967,296 (4 billions) in 10g when used BIGFILE Tablespace.

How to calcualte Max.Datafile Size:

Max.DataFile Size = db_block_size * Max. Number of Blocks

How to calculate Max.Database Size:

Max.Data file Size * Max. Number of Datafiles.
Here is the chart with SMALLFILE Tablespace (using 4 million blocks):

Block Size Max. Data File Size Max. Database Size
32 K 128 GB 8,388,608 GB
16 K 64 GB 4,194,304 GB
8 K 32 GB 2,097,152 GB
4 K 16 GB 1,048,579 GB
2 K 8 GB 524,288 GB


Here is the chart with BIGFILE Tablesapce (using 4 million blocks):

Block Size Max. Data File Size Max. Database Size
32 K 131,072 GB (128T) 8,589,934,592 GB
16 K 65,536 GB (64T) 4,294,967,296 GB
8 K 32,768 GB (32T) 2,147,483,648 GB
4 K 16,384 GB (16T) 1,073,741,824 GB
2 K 8,192 GB (8T) 536,870,912 GB

The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.

Note:
The BIGFILE syntax must be specified during the tablespace creation as such:
CREATE BIGFILE TABLESPACE my_ts;

The BIGFILE tablespace can ONLY have a SINGLE datafile.
By creating a tablespace using this syntax, Oracle increases the maximum number of blocks in a datafile from the 4 Million blocks to a maximum of 4 Billion.

Wednesday, August 24, 2011

Parallel Execution on the same node where it started in RAC




By default, in an Oracle RAC environment, a SQL statement executed in parallel can run across all of the nodes in the cluster. For this cross-node or inter-node parallel execution to perform, the interconnection in the Oracle RAC environment must be size appropriately because inter-node parallel execution may result in a lot of interconnect traffic. If the interconnection has a considerably lower bandwidth in comparison to the I/O bandwidth from the server to the storage subsystem, it may be better to restrict the parallel execution to a single node or to a limited number of nodes. Inter-node parallel execution does not scale with an undersized interconnection.

10g:
Utilize the parameters instance_group and parallel_instance_group to limit this execution to particular node.

ex.,
Instance#1 parameter:
instance_groups='pqgrp1','pqallnodes';
Instance#2 parameter:
instance_groups='pqgrp2','pqallnodes';

Now, While running the batch process or any program that needs to be executed in only one instance then:
alter session set parallel_instance_group = 'pqgrp1'; -- This will make the following program executions to be used only Instance#1.

11g on-wards:
To limit inter-node parallel execution, you can control parallel execution in an Oracle RAC environment using the PARALLEL_FORCE_LOCAL initialization parameter. By setting this parameter to TRUE, the parallel server processes can only execute on the same Oracle RAC node where the SQL statement was started.

Thursday, August 18, 2011

Managing multiple archive destinations with RMAN backup and restore process

Goal is to Explain how multiple archive destinations are handled during an RMAN backup and restore process

Specifying Archive Destinations

You can choose whether to archive redo logs to a single destination or multiplex them. If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters) or to archive only to a primary and secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).

Backing Up Archived Redo Log Files with BACKUP ARCHIVELOG

To back up archived redo logs, use the BACKUP ARCHIVELOG command at the RMAN prompt. This example uses a configured disk or sbt channel to back up one copy of each log sequence number for all archived redo logs:

BACKUP ARCHIVELOG ALL;

Even if your redo logs are being archived to multiple destinations and you use RMAN to back up archived redo logs, RMAN selects only one copy of the archived redo log file to include in the backup set. (Since logs with the same log sequence number are identical, there is no need to include more than one copy.)

RMAN Command: DELETE INPUT

DELETE INPUT will remove archivelogs from the first destination. It will also remove archivelogs from the second destination that was PREVIOUSLY backed up.

For example:

Backup #1

  • Backup archivelogs older than seven days from destination #1, and remove these files
  • Do not backup or remove any archivelogs in destination #2 has all files

Backup #2

  • For destination #1, backup archivelogs older than seven days and remove these files
  • For destination #2, backup archivelogs that satisfied backup criteria #1 (more than 14 days old) and remove them

Whereas a DELETE ALL INPUT will backup from one destination and delete both copies of the archivelog.

Backup and Delete from a Specified Archive Destination

If you'd like to only backup and remove from a single archive destination, use the LIKE clause:

eg:

RMAN> backup archivelog until time 'sysdate -7' like '/u04/oracle/admin/TEST/arch/%' delete
input;

The above will only backup and delete from the LIKE destination

If archive logs are backed up to multiple destinations, and if the required retention policy is required to be different for different destinations, then the following can be done:

RMAN> run {

backup archivelog all;
delete archivelog until time 'sysdate -1' like '/am3/oradata/arch/%';
delete archivelog until time 'sysdate -5' like '/am3/oradata/arch1/AM3P2/%';
}

The FRA and DELETE INPUT

RMAN will backup and remove the archivelogs in numerical order from _dest_1 to dest_10. However,
if one of the archive destinations is an FRA, RMAN will always backup and remove from the FRA
first, and then work on the numerical order of the log_archive_dest_x

eg.
If the following parameters were set:

  • log_archive_dest_1='location=/u002/oraarch/ORA1020
  • log_archive_dest_2='LOCATION=USE_DB_RECOVERY_FILE_DEST
  • log_archive_dest_3='location=/u002/oraarch3/ORA1020'

Oracle will backup and remove from the FRA, folllowed by archivelogs in log_archive_dest_1.
Finally, logs in log_archive_dest_3 will be removed.

Where do the restored archivelogs go?

During the restore process RMAN will check all archive destinations to make sure that the archivelog requested does not already exist. If the archivelog already exist in one of the destinations RMAN will not restore the file. If the archivelog does not exist in any of the destinations RMAN will restore it to the FRA if it exists. Otherwise it will restore it to the highest archive destination defined.


Restoring archivelog to FRA in ASM

When using the FRA and ASM, the archivelog will be restored to the current directory, rather than the time at which it was generated. Even if using the SET ARCHIVELOG DESTINATION, an alias will be created to the current directory.

For example, on the 16 Aug 2010, when restoring the 13 Aug archivelogs:

RMAN> run {
allocate channel c1 type 'sbt_tape';
set archivelog destination to '
+SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_13';
restore archivelog from time "to_date('13/08/2010:00:00:00','dd/mm/yyyy:hh24:mi:ss')"
until time "to_date('14/08/2010:00:00:00','dd/mm/yyyy:hh24:mi:ss')";
}

+SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_13
ASMCMD> ls -ltr
Type Redund Striped Time Sys Name
N 1_94528_708660567.dbf => +SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_16/thread_1_seq_94528.5341.727224533
N 1_94529_708660567.dbf => +SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_16/thread_1_seq_94529.3425.727224661
N 1_94530_708660567.dbf => +SHARED_FRA_DG01/P132/ARCHIVELOG/2010_08_16/thread_1_seq_94530.5644.727224779

PS: This content is an excerpt from oracle's metalink Article ID 443814.1 with few additional notes at the top.