Saturday, December 10, 2011

ORA-00059: maximum no. of DB_FILES exceeded


ORA-00059:
maximum number of DB_FILES exceeded
Cause:
The value of the DB_FILES initialization parameter was exceeded.
Action:
Increase the value of the DB_FILES parameter and warm start.

Today I came across ORA00059: maximum number of DB_FILES exceeded, while adding a tablespace to my database then I checked db_files parameter

This will give the MAXDATAFILES specified at control file level while creating the database

Select   value  from  v$parameter   where  name = ‘db_files’ ;

value
-------
500

Also I checked  v$control_record_section to see MAXDATAFILES 

select records_total from v$controlfile_record_section where type = ‘DATAFILE’;

records_total
----------------
700

Then I increased the db_files parameter

SQL> alter system set db_files=700 scope=spfile;
System altered.

And bounce the database to see the new value

select   value  from  v$parameter   where  name = ‘db_files’;
value
-------
700

You can still increase db_files to morethan control_record_section parameter

EX:  db_files= 800
      Control_record_section=700

But when you reach datafiles to near 700 then control_record_section automatically increases its value

Ex: select count(*) from dba_data_files;
Count(*)
------------
698

And still if you add one more to it

Ex: select count(*) from dba_data_files;

Count(*)
------------
699

Then you can check the control_record_section  parameter automatically increases its value

select records_total from v$controlfile_record_section where type = ‘DATAFILE’ ;

records_total
------------------
800

NOTE:
When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.
DB_FILES specifies the maximum number of database files that can be opened for this database.


Thursday, December 8, 2011

Avoid Inactive PUTTY session


I’m taking Cold backup of database which is of 2TB size with Rman  using putty  for that I connected to rman(database in mount stage)  and written the backup script with 1 channel to disk and left  to my home in the evening so rman created a backup piece after it has reached around 160gb it has to create another backup piece but due to putty which is in idle for more than 1 hour it became inactive and unable to create another backup piece this results in rman to stop backing up my database
    So in order to avoid this kind of situation here is the solution

STEP 1 :Go to PuTTY Configuration –> Connection and set the "seconds between keepalives" to a non-zero value. This ensures that your PuTTY client sends NULL packets to the server regularly. If you do not know the idle session timeout defined on your server, then choose a small value for the "seconds between keepalives". The value you select must be less than the idle session timeout of your server for PuTTY to keep your connection active. The default value is 0 which tells PuTTY not to bother about keeping the connection alive. Refer the picture below:


STEP 2 : Save Your PUTTY Configuration
So, after completing STEP 1, you must go to the session window and click the “SAVE” button.
Then your Putty session doesn’t go to inactive state even though you leave that for a long time.




Thursday, December 1, 2011

Bitmap & B-Tree indexes



What is Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree indexes are the regular type that OLTP systems make much use of, and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.

 

What is Bitmap Index?

A bitmap index is an indexing method that can provide both performance benefits and storage savings. Bitmap indexes are particularly useful for data warehousing environments because data is usually updated less frequently and ad hoc queries are more common.

Bit-mapped indexes are best for “low-cardinality” data (such as a column to indicate a person’s gender, which contains only two possible values: MALE and FEMALE).

 

Characteristic of Bitmap Indexes:

        1.  For columns with very few unique values (low cardinality)

        2.  Tables that have no or little insert/update are good candidates (static data in warehouse)

        3.  Stream of bits: each bit relates to a column value in a single row of table.

 

What is B-Tree Index?

B-tree indexes are usually created on columns containing the most unique values. It is an ordered set of entries in which each entry a search key value and a pointer to a specific row with that value. When a server finds a search key value matching a constraint, the pointer is used to fetch the row.



Tuesday, November 15, 2011

9i Export/Import Parameters


USERID:   username/password
BUFFER:   size of data buffer
FILE:   output file (EXPDAT.DMP)
COMPRESS:   import into one extent (Y)
GRANTS:   export grants (Y)
INDEXES:   export indexes(Y)
ROWS:   export data rows (Y)
CONSTRAINTS:   export table constraints (Y)
CONSISTENT:   cross-table consistency (N)
LOG:   log file of screen output (None)
STATISTICS:   analyze objects (ESTIMATE)
DIRECT:   Bypass the SQLcommand processing layer (N) (new in Oracle8)
FEEDBACK:   Show a process meter (a dot) every X rows exported (0 – Xvalue)
HELP:   Shows help listing MLS MLS_LABEL_FORMAT Used with secure Oracle; we won't cover
these.
FULL:   export entire file (N)
OWNER:   list of owner usernames
TABLES:   list of table names
RECORDLENGTH:  length of IO record
INCTYPE:  incremental export type
RECORD:   track incr. export (Y)
PARFILE:   parameter file name

How to get parent/child of a table in Oracle


This query gives list of children tables of a given table:


SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&tab');


And this one gives list of parent tables of a given table:

SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('&tab');

Saturday, November 12, 2011

Chmod commands


The UNIX chmod command is used to change the execution permissions of a UNIX file. The  chmod stands for change mode. The chmod command is based on the permissions and the chmod permissions can be assigned either by number or by a letter value.

Owner
Group
World
Meaning
7
7
7
Read + Write + execute
6
6
6
Write + execute
5
5
5
Read + execute
4
4
4
read only
2
2
2
write only
1
1
1
Execute only


Owner (u)
Group (g)
World(o)
Meaning
rwx
rwx
rwx
Read + Write + execute
rw
rw
rw
Read + Write
rx
rx
rx
Read + execute
wx
wx
wx
Write + execute
r
r
r
Read only
w
w
w
Write Only
x
x
x
Execute only

                 

Wednesday, November 9, 2011

Suspend & Resume a Database

                The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets us back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state. The suspend command is not specific to an instance. In an Oracle Real Application Clusters environment, when we issue the suspend command on one system, internal locking mechanisms propagate the halt request across instances, thereby quiescing all active instances in a given cluster. However, if someone starts a new instance another instance is being suspended, the new instance will not be suspended.

Using the ALTER SYSTEM RESUME statement to resume normal database operations. The SUSPEND and RESUME commands can be issued from different instances. For example, if instances 1, 2, and 3 are running, and we issue an ALTER SYSTEM SUSPEND statement from instance 1, then we can issue a RESUME statement from instance 1, 2, or 3 with the same effect. The suspend/resume feature is useful in systems that allow us to mirror a disk or file and then split the mirror, providing an alternative backup and restore solution. If we use a system that is unable to split a mirrored disk from an existing database while writes are occurring, then we can use the suspend/resume feature to facilitate the split. 

The suspend/resume feature is not a suitable substitute for normal shutdown operations, because copies of a suspended database can contain uncommitted updates.

The following statements illustrate suspend and resume usage. The V$INSTANCE view is queried to confirm database status.

     SQL>    alter system suspend;
               System altered.

     SQL>   select database_status from V$instance;
  
             DATABASE_STATUS
               ------------------------
                 SUSPENDED

       SQL>  alter system resume ;
                System altered.

      SQL> select database_status from v$instance ;
        
             DATABASE_STATUS
             -------------------------
              ACTIVE


Oracle Release Dates

Version
Release
Date
Oracle 6
1988
Oracle 7
1992
Oracle 8
1997
Oracle 8i
1998
Oracle 9i
2001
Oracle 10gR1
2003
Oracle 10gR2
July 2005
Oracle 11gR1
Aug 2007
Oracle 11gR2
Sep 2009

RMAN Backup Views


The following Oracle dynamic views can be used to obtain RMAN information stored in the control file:
  •  V$ARCHIVED_LOG:    show which archives have been created, backed up, and      cleared in the database
      
  •  V$BACKUP_CORRUPTION:    show which blocks have been found to be corrupt during a backup of a backup set             

  • V$BACKUP_DATAFILE:   useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also help you find the number of corrupt blocks in the datafile 

  •    V$BACKUP_DEVICE:   display information about supported backup devices. DISK is  not returned because it is always available

  •  V$BACKUP_FILES:   display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPYcommands

  •  V$BACKUP_PIECE:    show backup pieces created for backup sets 
  • V$BACKUP_REDOLOG:   show archived logs stored in backup sets 

  •  V$BACKUP_SET :    show backup sets that have been created 

  •  V$BACKUP_SPFILE:   display information about server parameter files in backup sets 
  • V$COPY_CORRUPTION:     show which blocks have been found to be corrupt during an image copy 

  • V$DATABASE_BLOCK_CORRUPTION:    list database blocks marked as corrupt during the most recent RMAN backup 

  • V$RMAN_CONFIGURATION:    list information about RMAN persistent configuration settings 

  • V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE:     proxy settings for RMAN
  • V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO:    backup performance statistics
Auto Scroll Stop Scroll