Physical Database Limits in oracle 11g

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of20,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASEstatement Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file	Maximum size	Dependent on the operating system. An external table can be composed of multiple files.

ORA-00257: archiver error

ORA-00257: archiver error


ORA-16014: log 4 sequence# 4614 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/u01/oracle/oracle/oradata//redo04a.log'

This is usually because the archive log drive has become full and needs to be cleared down. The first step is to make some more space on the archivelog drive. You can find out where you are archiving your logs to by looking at the init.ora or spfile and checking the log_archive_dest_n parameter. Normally log_archive_dest_1 is the local archiving location.

If you are having trouble actually logging on to the database to check the value for the archiving location just open up the SPFILE or INIT.ora file in a notepad and search for the parameter in there.

You also have the option to change the location of where the archivelogs are being written to, using the following command:

ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/Disk2/oraclelogs/ArchLogs' SCOPE=both;

If you are having issues logging onto the DB check out the strategy below which should get you logged on if your normal way of logging on doesn't work.

Once you have cleared down some space for new archive logs, you will need to log on to the database and clear the archive logs. You may not be able to log in to the database as you normally would. That being the case, this time you need log on using the following sequence:

set oracle_sid = TESTDB

sqlplus /nolog

conn sys/ as sysdba

archive log all;

Once complete, you should see the archive logs being created on the archive log drive and operation of your database should continue as normal, hopefully... If not, check the alert log and see what other errors you have.

It should be noted that if you need to keep the archive logs and they haven't been backed up you should perhaps move them somewhere temporarily until you can be sure they have been backed up before you delete them.

Alternate solution

Connect with rman and delete some old archivelogs,

[oracle@ip*********** ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 11 15:30:37 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL1 (DBID=!!!!!!!!!!!!)

RMAN> delete noprompt archivelog all completed before 'sysdate-7';

RMAN>crosscheck ARCHIVELOG ALL;

ORA-00376: file 13 cannot be read at this time

ORA-00376: file 3 cannot be read at this time

The other day going through my daily logs, I noticed after an unsuccessful disk move before the weekend, - seemed to have some issues.

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: "/my_path_to/ORACLE_SID/filename.dbf!

i logged myself onto LINUX at the same time to have a look inside v$datafile.

[] sqlplus / as sysdba

SQL> set pages 500

SQL> select * from v$datafile;

My interest in v$datafile is of course status, and status for file 3 was in RECOVER - so its a simple case right ? We need to recover the database.

[] sqlplus / as sysdba

SQL> RECOVER DATABASE;

ORA-00279: change 1306769 generated at 06/11/2007 11:57:12 needed for thread 1

ORA-00289: suggestion : "/my_path_to/ORACLE_SID/filename.arc"

ORA-00280: change 1306769 for thread 1 is in sequence #2245

SQL>

SQL>

sqlplus / as sysdba

SQL> startup mount;

SQL> alter database datafile "/my_path_to/ORACLE_SID/filename.dbs" offline;

SQL> recover datafile "/my_path_toil/ORACLE_SID/filename.dbs";

ORA-00279: change 1306769 generated at 06/11/2007 12:05:42 needed for thread 1

ORA-00289: suggestion : "/my_path_to/ORACLE_SID/filename.arc"

ORA-00280: change 1306769 for thread 1 is in sequence #2245

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.

Media recovery complete.

SQL> alter database datafile "/my_path_to/ORACLE_SID/filename.dbs" online;

SQL> alter database open;

  

ORA-39700:

ORA-39700:	database must be opened with UPGRADE option
Cause:	A normal database open was attempted, but the database has not been upgraded to the current server version.
Action:	Use the UPGRADE option when opening the database to run catupgrd.sql (for database upgrade), or to run catalog.sql and catproc.sql (after initial database creation).

ORA-01511: error in renaming log/data files ORA-01121: cannot rename data file – file is in use or recovery ORA-01110: data file : ‘datafile.dbf’

ORA-01511: error in renaming log/data files
ORA-01121: cannot rename  data file – file is in use or recovery
ORA-01110: data file <string>: ‘datafile.dbf’

To Move the datafile or rename the datafile, follow this Steps And also related with the above mentioned Errors

1.    Login to SQLPlus.

2.    Connect as SYS DBA with 

CONNECT / AS SYSDBA 

3.    Shutdown database with

 SHUTDOWN 

4.    Rename or/and move the datafiles at operating system level.

5.    Start Oracle database in mount state with 

STARTUP MOUNT 

6.    Modify the name or location of datafiles in Oracle dictionary using following command syntax:

ALTER DATABASE RENAME FILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’;

7.    Open Oracle database instance completely with 

ALTER DATABASE OPEN 

If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline.

1.    Login to SQLPlus.

2.    Connect as SYS DBA with 

CONNECT / AS SYSDBA .

3.    Make offline the affected tablespace with 

ALTER TABLESPACE <tablespace name> OFFLINE;

4.    Modify the name or location of datafiles in Oracle data dictionary using following  syntax:

ALTER TABLESPACE <tablespace name> RENAME DATAFILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’;

5.    Bring the tablespace online again with

 ALTER TABLESPACE alter tablespace <tablespace name> ONLINE; 

How to Drop Tablespace and Recover Database When Accidentally Delete Datafile

 You may get also some missing datafiles after database recovery process. In any case, missing a or a few datafiles will cause Oracle database to fail to startup, rendering the whole DB inaccessible. The solution to the missing datafiles is to drop the affected tablespace where has incomplete datafiles, and then recreate the tablespace and import the data into the tablespace from backup

1.    Run SQL*Plus.

2.    Connect to database as SYSDBA with this query:

CONNECT / AS SYSDBA

3.    Mount the database instead of starting it up:

STARTUP MOUNT;

4.    Issue the following command to bring the missing datafile offline so that Oracle won’t trying to connect and access the datafile anymore:

ALTER DATABASE DATAFILE ‘<datafile name with complete path>’ OFFLINE DROP;

Repeat the command for every datafiles that unaccounted for.

5.    Now start the database proper:

ALTER DATABASE OPEN;

6.    As the tablespace has damaged, drop it to recreate from fresh backup.

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

7.    Ensure the other datafiles for the tablespace has been deleted, if not, remove them manually from the operating system.

=========================================================================

To determine and identify all datafiles that link to a tablespace, use the following query, with tablespace name in capital letter:

SELECT file_name, tablespace_name
FROM dba_data_files
WHERE tablespace_name =’<tablespace name>’;

If a tablespace contains multiple datafiles, and you just want to drop one or some of the datafiles and keep the remaining datafiles together with the objects and contents, the objects or data must be exported for the affected table space. Once exported, the tablespace can be dropped with above “DROP TABLESPACE” command. Then, recreate the tablespace with the datafile(s) required (that you initially want to keep), and then import the objects into the recreated tablespace.

If one or more datafiles is missing after a recovery process or accidental deletion, you can use ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command to make the datafile offline so that database can starts up after which the troubled tablespace can be dropped.

====================================================================

8.    Continue with the recovery process.

--------------------------------------------------------------------------------------------------------------

RMAN-06059: expected archived log not found

One of our regularly scheduled jobs on PROD which backed up the archivelogs each day failed with the error message below:

RMAN-03002: failure of backup command
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /Disk2/Oraclelogs/LOGS/LOG_246800_1.ARC
ORA-27041: unable to open file
OSD-04002: unable to open file

The Cause

This happened because I had previously restored these two archivelogs to disk on PROD from a backup because we needed to use them for our data guard environment.

The Solution

First of all physically delete any files that you no longer require. Then, from RMAN on your production server, log in and issue the following:

CROSSCHECK ARCHIVELOG ALL;

This will ensure that RMAN has marked the logs which are not there as EXPIRED in the control file or recovery catalog, depending on which one you are using, so that they can easily be deleted from the catalog and/or control file of the database. The command below will prompt you before it removes the files, and will show you which files it will attempt to delete.

DELETE EXPIRED ARCHIVELOG ALL;

Next all you have to do is re-run the backup command and it should work