How to: ORA-00059: maximum number of DB_FILES exceeded

Symptom:

create tablespace data_tbs38 datafile "+DATA' size 100M;
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

Solution:

Increase db_files initialization parameter and bounce the database. Increase this parameter will incease the PGA consumation for every server process.

SQL> alter system set db_files=500 scope=spfile sid='*';

System altered.

How about MAXDATAFILES specified when creating database?

From Oracle8 onwards, Controlfile will auto-expend if MAXDATAFILES is smaller. similar inforamtion could be found in the alert log:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.0.1 - Beta
PL/SQL Release 11.2.0.0.1 - Beta
CORE    11.2.0.0.1      Beta
TNS for 32-bit Windows: Version 11.2.0.0.1 - Beta
NLSRTL Version 11.2.0.0.1 - Beta

SQL> select type,record_size rs, records_total rt, records_used ru,first_index f
  2  last_index li, last_recid lr from v$controlfile_record_section;

TYPE                             RS       RT  RU  FI  LI  LR
---------------------------- ------ -------- --- --- --- ---
DATABASE                        316        1   1   0   0   0
CKPT PROGRESS                  8180       11   0   0   0   0
REDO THREAD                     256        8   1   0   0   0
REDO LOG                         72       16   3   0   0   0
DATAFILE                        520       52  23   0   0  17
FILENAME                        524     2138  27   0   0   0
TABLESPACE                       68       40  24   0   0  18
TEMPORARY FILENAME               56       20   1   0   0   1
RMAN CONFIGURATION             1108       50   2   0   0   2
LOG HISTORY                      56      292   1   1   1   1
OFFLINE RANGE                   200       81   0   0   0   0

TYPE                             RS       RT  RU  FI  LI  LR
---------------------------- ------ -------- --- --- --- ---
ARCHIVED LOG                    584      308   3   1   3   3
BACKUP SET                       40      409   0   0   0   0
BACKUP PIECE                    736       44   0   0   0   0
BACKUP DATAFILE                 200       81   0   0   0   0
BACKUP REDOLOG                   76      215   0   0   0   0
DATAFILE COPY                   736       44   0   0   0   0
BACKUP CORRUPTION                44      371   0   0   0   0
COPY CORRUPTION                  40      409   0   0   0   0
DELETED OBJECT                   20      818   0   0   0   0
PROXY COPY                      928       88   0   0   0   0
BACKUP SPFILE                   124      131   0   0   0   0

TYPE                             RS       RT  RU  FI  LI  LR
---------------------------- ------ -------- --- --- --- ---
DATABASE INCARNATION             56      292   1   1   1   1
FLASHBACK LOG                    84     2048   0   0   0   0
RECOVERY DESTINATION            180        1   1   0   0   0
INSTANCE SPACE RESERVATION       28     1055   1   0   0   0
REMOVABLE RECOVERY FILES         32     1000   0   0   0   0
RMAN STATUS                     116      141   0   0   0   0
THREAD INSTANCE NAME MAPPING     80        8   8   0   0   0
MTTR                            100        8   1   0   0   0
DATAFILE HISTORY                568       57   0   0   0   0
STANDBY DATABASE MATRIX         400       31  31   0   0   0
GUARANTEED RESTORE POINT        212     2048   0   0   0   0

TYPE                             RS       RT  RU  FI  LI  LR
---------------------------- ------ -------- --- --- --- ---
RESTORE POINT                   212     2083   0   0   0   0
DATABASE BLOCK CORRUPTION        80     2045   0   0   0   0
ACM OPERATION                   104       64   5   0   0   0
FOREIGN ARCHIVED LOG            604     1002   0   0   0   0

37 rows selected.

Expanded controlfile section 6 from 20 to 40 records
The number of logical blocks in section 6 remains the same
Completed: create tablespace test12 datafile size 10m
create tablespace test13 datafile size 10m
Expanded controlfile section 4 from 20 to 52 records
Requested to grow by 32 records; added 1 blocks of records

How to enable powerpath failed path in a faster way

By unplug one of the Fiber cable to simulate FC HBA failure, the result as expect:

[root@VMXDB01 ~]# powermt display
Symmetrix logical device count=39
CLARiiON logical device count=0
Hitachi logical device count=0
Invista logical device count=0
HP xp logical device count=0
Ess logical device count=0
HP HSx logical device count=0
==============================================================================
----- Host Bus Adapters ---------  ------ I/O Paths -----  ------ Stats ------
###  HW Path                       Summary   Total   Dead  IO/Sec Q-IOs Errors
==============================================================================
   3 qla2xxx                       optimal      39      0       -     0      0
   4 qla2xxx                       failed       39     39       -     0     39


[root@VMXDB01~]# powermt display dev=emcpowera
Pseudo name=emcpowera
Symmetrix ID=0009899012321
Logical device ID=0141
state=alive; policy=SymmOpt; priority=0; queued-IOs=0
==============================================================================
---------------- Host ---------------   - Stor -   -- I/O Path -  -- Stats ---
###  HW Path                I/O Paths    Interf.   Mode    State  Q-IOs Errors
==============================================================================
   3 qla2xxx                   sdbe      FA  7aA   active  alive      0      0
   4 qla2xxx                   sdce      FA  9aA   active  dead       0      1

After put back the fiber cable, it takes 5 minutes to detect this recovery action and change the state to "alive".

How to make it faster? the answer is to use "powermt enable hba=#hba" to enable the hba (path) manually. Here #hba is referring to the first column of the output (hba is 3 or 4)

[root@VMXDB01 ~]# powermt enable hba=4

Using datapump to migrate data between databases

Objective:

Refresh data from VMXDB01 to VMXDB02

From Source VMXDB01

Create directory object

SQL> create directory logical_export as '/u01/logical_export';

Export data:

[oracle@VMXDB01 ~]$ expdp \'/ as sysdba\' directory=logical_export dumpfile=code.dmp logfile=code.log tables=donghua.tbl_code

Export: Release 10.2.0.4.0 - 64bit Production on Sunday, 27 September, 2009 21:12:29

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  '/******** AS SYSDBA' directory=logical_export dumpfile=code.dmp logfile=code.log tables=donghua.tbl_code                                                         
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DONGHUA"."TBL_CODE"                     3.553 MB   43960 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/logical_export/code.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 21:12:40

To Destination VMXDB02

Create directory object

SQL> create directory logical_export as '/u01/logical_export';

Import data:

[oracle@VMXDB02 ~]$ impdp \'/ as sysdba\' directory=logical_export dumpfile=code.dmp logfile=code.log full=y content=data_only table_exists_action=truncate

Import: Release 10.2.0.4.0 - 64bit Production on Sunday, 27 September, 2009 21:16:57

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  '/******** AS SYSDBA' directory=logical_export dumpfile=code.dmp logfile=code.log full=y content=data_only table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DONGHUA"."TBL_CODE"                     3.553 MB   43960 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 21:17:04

References:

TABLE_EXISTS_ACTION
Default: SKIP (Note that if CONTENT=DATA_ONLY is specified, the default is APPEND, not SKIP.)

Purpose

Tells Import what to do if the table it is trying to create already exists.

Syntax and Description

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}

The possible values have the following effects:

•SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

•APPEND loads rows from the source and leaves existing rows unchanged.

•TRUNCATE deletes existing rows and then loads rows from the source.

•REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

The following considerations apply when you are using these options:

•When you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.

•When you use SKIP, APPEND, or TRUNCATE, existing table-dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. For REPLACE, the dependent objects are dropped and re-created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.

•When you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action.

The existing table is loaded using the external tables access method if the existing tables have active constraints and triggers. However, be aware that if any row violates an active constraint, the load fails and no data is loaded.

If you have data that must be loaded, but may cause constraint violations, consider disabling the constraints, loading the data, and then deleting the problem rows before reenabling the constraints.

•When you use APPEND, the data is always loaded into new space; existing space, even if available, is not reused. For this reason, you may wish to compress your data after the load.

Restrictions

•TRUNCATE cannot be used on clustered tables or over network links.

Symmetrix parition alignment (Windows and Linux)

On Linux/Winows, first 63 blocks has been reserved for MBR (master boot record), and first data partition starts with offset at 63*512bytes=31.5KB.

However, Symmetrix array define tracks to be 64KB on DMX-3 and later (32KB for DMX-2 and earlier). This misalignment can use misalignment on Symmetrix memory cache (Just imaging one 64KB track on Host mapping to 2 tracks in the memory cache and storage), this will cause performance degradation.

Below steps shows how to manually perform the alignment using fdisk:
(128 blocks x 512bytes/block = 64KB)

[root@VMXDB01 ~]# fdisk /dev/emcpowera
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklab
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n             <-- New partition
Command action
   e   extended
   p   primary partition (1-4)
p                                   <-- Primary partition
Partition number (1-4): 1           <-- 1st partition
First cylinder (1-1011, default 1): <-- leave default to use 1st cylinder
Using default value 1 
Last cylinder or +size or +sizeM or +sizeK (1-1011, default 1011):  <-- Default
Using default value 1011

Command (m for help): x             <-- enter eXpert mode

Expert command (m for help): p      <-- print partition table

Disk /dev/emcpowera: 67 heads, 62 sectors, 1011 cylinders

Nr AF  Hd Sec  Cyl  Hd Sec  Cyl     Start      Size ID
 1 00   1   1    0  66  62 1010         62    4199632 83
 2 00   0   0    0   0   0    0          0          0 00
 3 00   0   0    0   0   0    0          0          0 00
 4 00   0   0    0   0   0    0          0          0 00

Expert command (m for help): b      <-- move to beginning of the partition
Partition number (1-4): 1           <-- choose 1st partition
New beginning of data (62-4199693, default 62): 128     <-- Enter starting block

Expert command (m for help): p      <-- print partition table

Disk /dev/emcpowera: 67 heads, 62 sectors, 1011 cylinders

Nr AF  Hd Sec  Cyl  Hd Sec  Cyl     Start      Size ID
 1 00   1   1    0  66  62 1010        128    4199566 83
Partition 1 has different physical/logical beginnings (non-Linux?):
     phys=(0, 1, 1) logical=(0, 2, 5)
 2 00   0   0    0   0   0    0          0          0 00
 3 00   0   0    0   0   0    0          0          0 00
 4 00   0   0    0   0   0    0          0          0 00

Expert command (m for help): w      <-- save partition table
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@VMXDB01 ~]# fdisk -l /dev/emcpowera

Disk /dev/emcpowera: 2150 MB, 2150891520 bytes
67 heads, 62 sectors/track, 1011 cylinders
Units = cylinders of 4154 * 512 = 2126848 bytes

         Device Boot      Start         End      Blocks   Id  System
/dev/emcpowera1               1        1011     2099783   83  Linux
Partition 1 has different physical/logical beginnings (non-Linux?):
     phys=(0, 1, 1) logical=(0, 2, 5)

Exploring EMC Storage: Introducing Symmetrix Software

EMC Solutions Enabler Kit

Contains all the base management software that provides a host with SYMAPI-shared libraries and the basic Symmetrix command line interface (SYMCLI)

What is SRDF?

The Symmetrix Remote Data Facility (SRDF) is a business continuity solution to replicate between Symmetrix storage arrays.

SRDF currently supports the followin modes of operation:

1. Synchronous mode (SRDF/S)
2. Asynchronous mode (SRDF/A)
3. Adaptive copy mode (transferring data regardless of order/consistency)

What is EMC TimeFinder?

TimeFinder is replicating data within a Symmetrix storage array.

• TimeFinder/Mirror - Genral monitor and control operations for Business continuance Volumes (BCV)
• TimeFinder/CG - Consistency groups
• TimeFinder/Clone - Clone copy
• TimeFinder/Snap - Snap copy

What is PowerPath?

PowerPath is host-based software that works with networked storage systems to intelligently manage I/O paths. PowerPath manages multiple paths to a storage array. Supporting multiple paths enables recovery from path failure because PowerPath automatically detects path failures and redirects I/O to other available paths.

A key feature of path management in PowerPath is dynamic, multipath load balancing.

What is EMC Replication Manager?

Replication Manager has a generic storage technology interface that allows it to connect and invoke replication methodologies available on:

• EMC Symmetrix arrays
• EMC CLARiiON arrays
• HP StorageWorks arrays

Exploring EMC Storage: Introducing Clariion Storage

Clariion array was originally designed for AViiON computer within Data General Corporation, and acquired by EMC on 1999. Occupying the entry-level and mid-range of EMC's SAN disk array product palette, it is complemented by the high-end Symmetrix.

CLARiiON AX-Series

• AX4: level disk array, support upto 60 SAS/SATA disks through expension pack, with total capacity 60TB

CLARiiON CX-Series

• CX-300/CX-300i/CX-500/CX-500i/CX-700: End-of-Life Product, each with unique data-in-place upgrade to the next level. ("i" inside the product name refers to "iscsi")


• CX3 Model 10/CX3 Model 20/CX3 Model 40/CX3 Model 80: each with unique data-in-place upgrade to the next level.

• CX4 Model 120/CX4 Model 240/CX4 Model 480/CX4 Model 960: each with unique data-in-place upgrade to the next level. Number (120/240/480/960) in the product represents maximum disk drives, and maximum capacity.

Reference:

http://en.wikipedia.org/wiki/Clariion

http://www.dg.com/collateral/hardware/comparison/emc-clariion.htm

Exploring EMC Storage: Introducing Symmetrix Storage

Symmetrix storage is designed for high-end, contrasting to Clariion, which is targeting to entry-midsize market.

These EMC Symmetrix product before DMX-2 have been omitted.

DMX-2 configurations: (Based on Direct Matrix Architecture)

• Symmetrix DMX-800EL: 8-60 drives, 4-64 GB cache memory, 8 x 2 GB FC front ends, modular rack mount
  
• Symmetrix DMX-800: 60-120 drives, 4-64 GB cache memory, 16 x 2 GB FC front ends, modular rack mount
  
• Symmetrix DMX-1000: up to 144 drives, 64-128 GB cache memory, 48 x 2 GB FC front ends, single cabinet
  
• Symmetrix DMX-2000: up to 288 drives, 128-256 GB cache memory, 64 x 2 GB FC front ends, two cabinets
  
• Symmetrix DMX-3000: up to 576 drives, 128-256 GB cache memory, 64 x 2 GB FC front ends, three cabinets

DMX-3 configurations:

• Symmetrix DMX-3 950: non-expandable version of Symmetrix, up to 360 drives, up to 128 GB cache memory, 8 front-end ports, one or two cabinets
  
• Symmetrix DMX-3: up to 2400 drives, up to 512 GB cache memory, 64 x 2 GB FC front ends (even 4 Gb FC's are available at this time), two to nine cabinets

DMX-4 configurations:

• Symmetrix DMX-4: up to 1920 drives (or up to 2400 drives for 500 GB drive only configurations), up to 512 GB cache memory, 64 x 4 GB FC front ends, two to nine cabinets

V-Max configurations: (Based on Virtial Matrix Architecture)

• Symmetrix V-Max SE: a entry point single enginee storage system
• Symmetrix V-Max: high-end storage array that scales from a single configuration with a dedicated system cabinet and a single storage bay to a larger eight-engine configuration with up to 10 storage bays capable of holding 2400 physical disk drivers.

Reference:

http://en.wikipedia.org/wiki/EMC_Symmetrix

EMC Symmetrix DMX Architecture Product Description Guide

Oracle Databases on EMC Symmetrix Storage Systems