Skip to content

Archive for

Tablespace Maximum Size

Using a bigfile tablespaces witha block size of 32k, a datafile can be as large as 128TB, with a max database size of 8EB.A database using only smallfile tablespaces can have a max datafile size of 128GB and therefor a max db size of 8PB. because a Bigfile tablespace can only have one datafile.

Given that the max number of datafiles in a database on most platforms is 65,533 and the no of blocks in a bigfile tablespace is 2 power of 32, you can calculate the max anount of space(M) in a single Oracle database as the maximum no of datafiles(D) multiplied by the maximum number of blocks per datafile(F) multiplied by the tablespace block size (B)


The max database size, give the max block size and max no of datafiles is

65,533 datafiles * 4,294,967,296 blocks per datafile * 32,768 block size = 9,223,231,299,366,420,480= 8EB

For a small file tablespace, no of blocks in a smallfile tbs datafile is only 2 power of 22.
65,533 datafiles *4,194,304 blocks per datafile * 32,768 block size = 8PB


ora-25153 temporary tablespace empty

When i going to start the export of a one user, it shows error ,

ora-25153 temporary tablespace empty.

Solution :

Add one temp file for temporary tablespace

sql>alter tablespace temp add datafile ‘D:\oracle\product\10.2.0\oradata\orcl\temp02.dbf’ size 10m;

Tablespace altered.

Now i can able to start export.

view: dba_temp_files

RMAN-Restore controlfile error

RMAN-Restore Controlfile

Resizing/Recreating Online Redolog file

Step 1 :- Check your online redo log file group and members

sql> select, a.member, b.bytes
  2  FROM v$logfile a, v$log b WHERE =;

group#                  member                                                                          bytes

1           D:\oracle\product\10.2.0\oradata\orcl\redo01.log           52428800

2          D:\oracle\product\10.2.0\oradata\orcl\redo02.log          52428800

3          D:\oracle\product\10.2.0\oradata\orcl\redo03.log          52428800

Step 2:

Forcing a log switch logfile

step 3:

sql> select group#, status from v$log;

group#       status

1                     inactive

2                   current

3                    inactive

sql> alter system switch logfile;

sql> alter system switch logfile;

sql> select group#, status from v$log;

group#       status

1                   active

2                   active

3                 current

Now we try to drop the group 1, we can face errors

ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)
ORA-00312: online log 1 thread 1: ‘file_name’

sql>alter system checkpoint global;

system altered.

sql> alter database drop logfile group 1;

Database altered.

Step 4: Recreate the redolog file

SQL> alter database add logfile group 1 (‘D:\oracle\product\10.2.0\oradata\orcl\redo01.log’) size 100m reuse;

Database altered.

sql> select group#, status from v$log;

group#       status

1                     unused

2                  in active

3                    current


  • UNUSED Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
  • CURRENT Current redo log. This implies that the redo log is active. The redo log could be open or closed.
  • ACTIVE Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
  • CLEARING Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
  • CLEARING_CURRENT Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
  • INACTIVE Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
  • INVALIDATED Archived the current redo log without a log switch.

Why we are not altering Resize logfile

Refer :

Change Archivelog mode

Change database from noarchivelogmode to archivelogmode

Check your database either archive log mode or no archive log mode

sql> archive log list;
database log mode noarchive
automatic archival disable

If you want change database from no archive log mode to archive log mode…

Follow steps

sql> shutdown immediate

database shutdown.

sql>startup mount

Database mounted.

sql>archive log list;
database log mode noarchive
automatic archival disable

sql>alter database archivelog;

database altered.

sql>archive log start;

sql>show parameter log_archive_start


sql>alter system set log_archive_start=true scope=spfile;
System altered.

(Note Skip “Sql>archive log start” this  step if you are using oracle 10g.Otherwise ORA-32004: obsolete and/or deprecated parameter(s) specified error will be occur.

If  you did wrong, check this link )

sql>shutdown immediate


database opened.

sql>archive log list

database log mode Archive
automatic archival Enable

If you want do change archive destination

sql>alter system set log_archive_dest =’\oracle\admin\’ scope=spfile;
System altered.

sql>shutdown immediate

Database shutdown.


database open.

sql>archive log list
database log mode archive
automatic archival enable
Location ‘\orace\admin\’

Clonig DB wihtout SYSAUX TBS is possible

Cloning DB without Sysaux Tablespace is possible ?

Welcome To Azar World