Archive

Archive for April, 2013

Temporary Tablespaces

April 26, 2013 Leave a comment

What are Temporary Tablespaces:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

Considerations:

Some performance considerations for temporary tablespaces:

– Always use temporary tablespaces instead of permanent content tablespaces for sorting (no logging and uses one large sort segment to reduce recursive SQL and ST space management enqueue contention).
– Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed (Use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space).
– Always use TEMPFILEs instead of DATAFILEs (reduce backup and recovery time + other advantages as described above)
– Stripe your temporary tablespaces over multiple disks to alleviate possible disk contention and to speed-up sorting operations (user processes can read/write to it directly).

 

Dropping / Recreating Temporary Tablespace

 Before dropping a temporary tables make sure that no users are connected.

 Also you cannot drop a default temp tablespace. You need to create another temp tablespace as default and drop the earlier.

 Below are the steps

 SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/the/full/path/to/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

 

Dropping a temp file

SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' 
                                  DROP INCLUDING DATAFILES;

 

Altering a temp file

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' 
                                           DROP INCLUDING DATAFILES;
(For locally managed temp tbspaces)
SQL> alter tablespace <your_temp_ts> shrink space keep 128M; 

 

If users are currently accessing the tempfile you are attempting to drop, you may receive the following error:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/BHARATDB/temp01.dbf' 
                                           DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' 
                                           DROP INCLUDING DATAFILES;
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

If this happens, you should attempt to drop the tempfile 
again so the operation is successful:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/BHARATDB/temp01.dbf' 
                                            DROP INCLUDING DATAFILES;
 Database altered.

 

 

 

Advertisements
Categories: Oracle Basics Tags:

Automate database Startup and Shutdown

April 25, 2013 Leave a comment

To automate the database startup and shutdown perform the below steps

1. Add the below entry /etc/oratab file

bharatdb:/u01/app/oracle/product/11.2.0/db_2:Y

2. Prepare the script /etc/init.d/dbora as root user

vi dbora
#!/bin/sh
 # chkconfig: 345 99 10
 # description: Oracle auto start-stop script.
 #
 # Set ORA_HOME to be equivalent to the $ORACLE_HOME
 # from which you wish to execute dbstart and dbshut;
 #
 # Set ORA_OWNER to the user id of the owner of the
 # Oracle database in ORA_HOME.
ORA_HOME=/u01/app/oracle/product/11.2.0/db_2; export ORA_HOME;
 ORA_OWNER=oracle; export ORA_OWNER;
 #export ORACLE_UNQNAME=DB11G
if [ ! -f $ORA_HOME/bin/dbstart ]
 then
 echo "Oracle startup: cannot start"
 exit
 fi
case "$1" in
 start)
 # Start the Oracle databases:
 # The following command assumes that the oracle login
 # will not prompt the user for any values
 echo -n "Starting Oracle Databases: " > /var/log/oracle
 echo "-------------------------------------------" >> /var/log/oracle
 date +"! %T %a %D : Starting Oracle Databases as 
                                part of system up." >> /var/log/oracle
 echo "-------------------------------------------" >> /var/log/oracle
 su $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
 echo "Done." >> /var/log/oracle
 echo "" >> /var/log/oracle
 echo "-------------------------------------------" >> /var/log/oracle
 date +"! %T %a %D : Finished." >> /var/log/oracle
 echo "-------------------------------------------" >> /var/log/oracle
 touch /var/lock/subsys/oracle
 ;;
 stop)
 # Stop the Oracle databases:
 # The following command assumes that the oracle login
 # will not prompt the user for any values
 echo -n "Shutting Down Oracle Listeners: " >> /var/log/oracle
 echo "--------------------------------------------" >> /var/log/oracle
 date +"! %T %a %D : Shutting Down Oracle Databases as part 
                                    of system down." >> /var/log/oracle
 echo "--------------------------------------------" >> /var/log/oracle
 su $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
 echo "Done."  >> /var/log/oracle
 echo ""
 echo "--------------------------------------------" >> /var/log/oracle
 date +"! %T %a %D : Finished." >> /var/log/oracle
 echo "--------------------------------------------" >> /var/log/oracle
 rm -f /var/lock/subsys/oracle
 ;;
 esac

3. Grant the necessary privileges

chmod 750 /etc/init.d/dbora

4. Make this script run automatically with the below command

chkconfig --add dbora

Now the databases will startup/shutdown accordingly during server reboot

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!