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)

M=D*F*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

Resizing/Recreating Online Redolog file

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

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

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

 
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
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

V$LOG

• 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 :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:55812348055

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

value
—–
False

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

http://azardba.blogspot.com/2008/11/ora-32004-obsolete-andor-deprecated.html )

sql>shutdown immediate

sql>startup

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.

sql>startup

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 ?

http://forums.oracle.com/forums/thread.jspa?threadID=984041&tstart=0