Posts Tagged ‘Oracle Database’

Problem Description
Drop undo tablespace fails with error Ora-01548 .
SQL> drop Tablespace UNDOTBS1;
drop Tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU1$’ found, terminate dropping
tablespace

Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segments.

Solution of The Problem
In order to get rid of this error you have to follow following steps.
1)Create pfile if you started with database with spfile.
SQL>Create PFILE from SPFILE;

2)Edit pfile and set undo management to manual.
undo_management = manual

3)Put the entries of the undo segments in the pfile by using the following statement in the pfile:

_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,…..)

4)Mount the database using new pfile.
Startup mount pfile=’fullpath to pfile’

5)Drop the datafiles,
sql>Alter Database datafile ‘&filename’ offline drop;

6)Open the database.
sql>Alter Database Open;

7)Drop the undo segments,

sql>Drop Rollback Segment “_SYSSMU1$”;
……
8)Drop Old undo tablespace.
sql>Drop Tablespace old_tablespace_name Including Contents;

9)Add the new undo tablespace.

10) Shutdown Immediate;

11) Change the pfile parameters back to Undo_management = AUTO and modify the parameter Undo_tablespace=new_undo_tablespace_name and remove the _offline_rollback_segments parameter.

12) Startup the Database.

Link: http://arjudba.blogspot.com

1. Dump a trace of the controlfile.
Use SQL*Plus (or Server Manager) and enter:
SQL> alter database backup controlfile to trace ;
2. Modify the created trace file (located in the user_dump_dest directory):
a) remove from the first line to the line before “STARTUP NOMOUNT”
b) remove line “ALTER DATABASE OPEN” to the end of the file
c) change value of maxdatafiles, maxlogfiles and other parameters as desired
d) rename the file to something .sql (such as cntl.sql)
3. Shutdown the database.
4. Backup the database.
5. Run Server Manager/SQLPLUS and do the following:
SVRMGR> connect internal or connect /as sysdba
SVRMGR> @cntl.sql (The SQL script that you created in step 2) or SQLPLUS>@cntl.sql
SVRMGR> alter database open noresetlogs ; or SQLPLUS> alter database open noresetlogs

SQLPLUS if you are using 9i SVRMGRL if you are using 8i or below.

Also Please read the following article from metalink…

Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 14-JAN-2005

PURPOSE
This article describes how you can recreate your controlfile.

SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.

WARNING:
——–

You should only need to recreate your control file under very special
circumstances:

– All current copies of the control file have been lost or are corrupted.

– You need to change a “hard” database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
MAXLOGHISTORY, etc.

– You are restoring a backup in which the control file is corrupted or
missing.

– Oracle Customer Support advises you to do so.

– If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.

We can install different oracle versions on same machine like Oracle 8i,9i or 10g. Also, we can create multiple database/instance on a single machine but remember to name them differently, for  example: db1,db2,db3 and so on. When name them like that, we avoid conflicting installing them on our single machine.

Suppose we planned to install oracle 8i,9i or 10g on same machine then we need to set oracle enviourment variable to point LATEST VERSION. Like this one:

ORACLE_SID=SIDNAME
ORACLE_HOME=10ORACLECHOME
PATH=$ORACHOME/bin(10G)
TNS_ADMIN=$ORACLE_HOME/network/admin(10g)
After that, we can configure LISTENER.ORA we use for three different version. There is two option for this.
First one: create separate listener.ora file for each version like

  1. listener with default port(1521)
  2. listener1 with separate port
  3. listener2 with separate port

    or

Second one: use only one listener.ora file for all three oracle homes. To do so, please follow following instructions:
1. Stop listener services of Oracle 8i,9i database
2. create listener.ora file for 10g. and edit 10g listener.ora file then do configure for oracle 8i or 9i database like below:

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
(SID_LIST=

(SID_DESC=
(GLOBAL_DBNAME=Ora8i)
(ORACLE_HOME=/oracle8i)
(SID_NAME=Ora8i))

(SID_DESC=
(GLOBAL_DBNAME=Ora9i)
(ORACLE_HOME=/oracle9i)
(SID_NAME=Ora9i))

(SID_DESC=
(GLOBAL_DBNAME=Ora10g)
(ORACLE_HOME=/oracle10g)
(SID_NAME=Ora10g))

(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle10g)
(PROGRAM=extproc)))

So please enjoy,

Minh Tran

Quite confuse when you want to re-install oracle-xe on Ubuntu. Struggling for several hours googling, I found this tip:

1. Run “sudo dpkg -P oracle-xe-universal” to get Ubuntu remove all the files as well as configuration files.

2. Delete folder: “/usr/lib/oracle/xe”

3. Restart the machine

4. Install the oracle-xe package.

5. Run “sudo /etc/init.d/oracle-xe configure" to have oracle-xe be setup

6. Access the nice Oracle web interface by pointing your Firefox to: http://127.0.0.1:8080/apex to check if everything went fine...

7. Have fun…

^^

MinhTran

Wanna “clear” the temp default tablespace, huh? Answer is NO WAY!

Luckily you can create a new TEMPORARY TABLESPACE and set it as default.

Then  Drop the old one…

increase size for the new TEMP TABLESPACE.

And there you go…

Minh-Tran