Wednesday, 17 July 2013

Moving Undo Tablespace from one location to another location

Create a new UNDO_TABLESPACE
2. Type "ALTER SYSTEM SET UNDO_TABLESPACE=NEW TABLESPACE". If using pfile, change the parameter on init.ora. If using spfile add the clause SCOPE=BOTH

All the active transactions will remain using the actual tablespace, but the new ones will be addressed to the new undo tablespace. Once the active transactions finish, you can put your old tablespace offline and then drop it, until then it will be "PENDING OFFLINE".

About the redo logs, just create new groups and drop the old ones. Remember you must have at least 2 groups at any given time, so you'll need to "add a new one/remove an old one" and so on.

I wanted to drop the existing Undo tablespace (undoTbs1), so I did the following:

1) I created a new undo tablespace 'undoTbs2'
2) Alter System SET Undo_Tablespace=undoTbs2

Then I tried the following :

DROP TABLESPACE undoTbs1
this did not work
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

I tried this also
DROP TABLESPACE undoTbs2 INCLUDING CONTENTS
this also did not work same error.

Can anyone help me in accomplishing this.

Thanks in advance
qAnand

The problem is that there is someone that still has a transaction with active undo segments. You could....

1) wait until all pending transactions using the old undo tablespace have commited or rolled back... you're the DBA(!)... surely we don't have to wait on the user, or

2) kill the offending session(!), or

3) restart your instance.

Afterwards, you should be able to drop the old undo tablespace.

JoeB

Check your alert log file and if you getting "Undo Tablespace X moved to Pending Switch-Out state." error message than you need to find the pending transactions which blocking you to drop UNDO tablespace. Use query below to find the pending offline transaction.

SELECT
a.usn,
a.name,
b.status,
c.tablespace_name,
d.addr,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser
FROM
v$rollname a,
v$rollstat b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE
a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr AND
b.status='PENDING OFFLINE';

You can either kill the transaction or call the user to commit his/her transaction. After this you should be able to drop UNDO tablespace.

Check metalink note 341372.1 for more info.

Move/rename datafiles in Oracle

Moving datafiles of a database: The datafiles reside under /home/oracle/OraHome1/databases/ora9 and have go to /home/oracle/databases/ora9.

SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;

TABLESPACE_NAME                SUBSTR(FILE_NAME,1,70)

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

SYSTEM                         /home/oracle/OraHome1/databases/ora9/system.dbf

UNDO                           /home/oracle/OraHome1/databases/ora9/undo.dbf

DATA                           /home/oracle/OraHome1/databases/ora9/data.dbf

SQL> select member from v$logfile;

MEMBER

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

/home/oracle/OraHome1/databases/ora9/redo1.ora

/home/oracle/OraHome1/databases/ora9/redo2.ora

/home/oracle/OraHome1/databases/ora9/redo3.ora

SQL> select name from v$controlfile;

NAME

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

/home/oracle/OraHome1/databases/ora9/ctl_1.ora

/home/oracle/OraHome1/databases/ora9/ctl_2.ora

/home/oracle/OraHome1/databases/ora9/ctl_3.ora

Now, as the files to be moved are known, the database can be shut down:

SQL> shutdown

The files can be copied to their destination:

$ cp /home/oracle/OraHome1/databases/ora9/system.dbf  /home/oracle/databases/ora9/system.dbf

$ cp /home/oracle/OraHome1/databases/ora9/undo.dbf    /home/oracle/databases/ora9/undo.dbf

$ cp /home/oracle/OraHome1/databases/ora9/data.dbf    /home/oracle/databases/ora9/data.dbf

$ cp /home/oracle/OraHome1/databases/ora9/redo1.ora   /home/oracle/databases/ora9/redo1.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo2.ora   /home/oracle/databases/ora9/redo2.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo3.ora   /home/oracle/databases/ora9/redo3.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_1.ora   /home/oracle/databases/ora9/ctl_1.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_2.ora   /home/oracle/databases/ora9/ctl_2.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_3.ora   /home/oracle/databases/ora9/ctl_3.ora

The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.

$ cp /home/oracle/OraHome1/dbs/initORA9.ora /home/oracle/databases/ora9/init.ora

The new location for the control files must be written into the (copied) init.ora file:

/home/oracle/databases/ora9/init.ora

control_files = (/home/oracle/databases/ora9/ctl_1.ora,

                 /home/oracle/databases/ora9/ctl_2.ora,

                 /home/oracle/databases/ora9/ctl_3.ora)

$ sqlplus "/ as sysdba"

SQL> startup exclusive mount pfile=/home/oracle/databases/ora9/init.ora

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/system.dbf' to '/home/oracle/databases/ora9/system.dbf';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/undo.dbf'   to '/home/oracle/databases/ora9/undo.dbf';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/data.dbf'   to '/home/oracle/databases/ora9/data.dbf';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo1.ora'  to '/home/oracle/databases/ora9/redo1.ora';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo2.ora'  to '/home/oracle/databases/ora9/redo2.ora';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo3.ora'  to '/home/oracle/databases/ora9/redo3.ora';

SQL> shutdown

SQL> startup pfile=/home/oracle/databases/ora9/init.ora

How to Drop UNDO Tablespace

It is not an easy task to drop the undo tablespace . Once I have to delete the undo tablespace due to some reason and i find that it is not straight forward to delete the undo tablespace . I got the following error while dropping the error :

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
UNDOTBS1 D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
SYSAUX D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
SYSTEM D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
EXAMPLE D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

As the error indicate that the undo tablespace is in use so i issue the following command.

SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace.

Therefore, to drop undo tablespace, we have to perform following steps:

1.) Create new undo tablespace
2.) Make it defalut tablepsace and undo management manual by editting parameter file and restart it.
3.) Check the all segment of old undo tablespace to be offline.
4.) Drop the old tablespace.
5.) Change undo management to auto by editting parameter file and restart the database

Step 1 : Create Tablespace : Create undo tablespace undotbs2

SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF' size 100M;
Tablespace created.

Step 2 : Edit the parameter file

SQL> alter system set undo_tablespace=UNDOTBS2 ;
System altered.

SQL> alter system set undo_management=MANUAL scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 426852352 bytes
Fixed Size 1333648 bytes
Variable Size 360711792 bytes
Database Buffers 58720256 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2

Step 3: Check the all segment of old undo tablespace to be offline

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ ------------------------------ ------------------------------ ----------------
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU10_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU1_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU3_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU4_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU5_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU6_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU7_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU8_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU9_1192467665$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU12_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU13_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU14_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU15_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU11_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU17_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU18_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU19_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU20_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU16_1304934663$ UNDOTBS2 OFFLINE

21 rows selected.

If any one the above segment is online then change it status to offline by using below command .

SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Step 4 : Drop old undo tablespace

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

Step 5 : Change undo management to auto and restart the database

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 426852352 bytes
Fixed Size 1333648 bytes
Variable Size 364906096 bytes
Database Buffers 54525952 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.

SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2

Enjoy J J J

Switch Undo Tablespace

SQL> select name from v$tablespace where name like 'UNDO%';

NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace = 'UNDOTBS2';

System altered.

On another session which connect as scott user

SQL> insert into test select * from all_objects;

47327 rows created.

SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE

14 rows selected.

On another session which connect as scott user

SQL> commit;

Commit complete.
SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

11 rows selected.
SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2

Note: When you change Undo Tbs say undotbs1 to undotbs2 then all existing trasaction they using UNDOTBS1 they still using undotbs1 and status show "OFFLINE PENDING".and all new transaction after changed undotbs "alter system undo_tablespace" statements issued used New Undo Tablespace.

You cann't drop or offline if status show offline pending.
when all transaction which using old undotbs are COMMIT;
then you can drop or offline old undotbs.

SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE

14 rows selected.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace

st it again
https://<hostname>:1158/em/console

Tablespace shrinking

iam using oracle 9i R2 and i want to reduce my datafile size but it's show's that error when i try to resize it. ORA-03297

If you try to resize a datafile to a size smaller than is needed to contain all
the database objects in that datafile, you will get an error:

ORA-03297: file contains <number> blocks of data beyond requested
RESIZE value

Hi,

We can directly resize datafiles

1.TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

FILE_NAME

     BYTES

/.../dbsGNX.dbf

419430400

2.TEST.SQL>ALTER DATABASE DATAFILE '/.../dbsGNX.dbf' RESIZE 390M;
Database altered.
Or Error

ORA-03297: file contains <number> blocks of data beyond requested
RESIZE value

3.TEST.SQL>SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

FILE_NAME

     BYTES

/.../dbsGNX.dbf

408944640

But the minimum file size is the size of the extend the furthest in the datafile:

4.TEST.SQL>SELECT FILE_ID,FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='SYSTEM';

   FILE_ID

FILE_NAME

/.../dbsGNX.dbf

5.TEST.SQL>SELECT MAX(BLOCK_ID) MBID FROM DBA_EXTENTS WHERE FILE_ID=1;

MBID

6.TEST.SQL>SELECT SEGMENT_NAME,OWNER,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID=1 AND BLOCK_ID=25129;

SEGMENT_NAME                                                                      OWNER                          SEGMENT_TYPE

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

I_OBJAUTH2                                                                        SYS                            INDEX

7.TEST.SQL>SHOW PARAMETER BLOCK_SIZE

NAME                                 TYPE                             VALUE

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

db_block_size                        integer                          8192

8.TEST.SQL>SELECT 8192*25129 FROM DUAL;

8192*25129

205856768

about 200M.

purge Recyclebin

Enable the database recyclebin

The recycle bin is enabled by default.

SQL> ALTER SYSTEM SET recyclebin = ON;

To disable for the entire database (not recommended):

SQL> ALTER SYSTEM SET recyclebin = OFF;

To enable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = ON;

To disable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = OFF;

[edit] Show recyclebin contents

To see the objects in the recyclebin:

SHOW RECYCLEBIN

[edit] Clear recyclebin

To remove all dropped objects from the recyclebin (current user):

PURGE RECYCLEBIN;

To remove all dropped objects from the recyclebin (system wide):

PURGE DBA_RECYCLEBIN;

Tables can also be droped without sending them to the recyclebin. Example:

DROP TABLE t1 PURGE;

[edit] Examples

Drop a table:

SQL> DROP TABLE t1;

Undrop the table:

SQL> FLASHBACK TABLE t1 TO BEFORE DROP;

Old Files deletion

find /u02/lax_backup_newexis/*.dmp -mtime +10 -exec rm {} \;

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

#!/bin/bash

clear

######################################################################

#################### old File Deletion ###############################

#################### dba.amr@gmail.com ###############################

export PATH

# Oracle ENV

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/opt/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

find /home/oracle/logicalbak/ -name '*.dmp*' -mtime +60 -exec rm {} \;

find /home/oracle/logicalbak/ -name '*.log*' -mtime +60 -exec rm {} \;

ls -l, df -h , df -k

ls -l:

[oracle@adcpblr LogicalBackup]$ ls -l
total 18573916
-rw-r--r-- 1 oracle oinstall 9511075840 Nov 10 11:52 nov10.dmp---9511075840 bytes--9511075Kb--9511Mb--9.5gb
-rw-r--r-- 1 oracle oinstall 918715 Nov 10 11:52 nov10.log
-rw-r--r-- 1 oracle oinstall 9488154624 Nov 4 11:36 nov4.dmp
-rw-r--r-- 1 oracle oinstall 918227 Nov 4 11:36 nov4.log

df -h:

[oracle@adcpblr LogicalBackup]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda10 147G 89G 51G 64% /-----------------------52Gb
/dev/sda1 487M 17M 445M 4% /boot
none 2.0G 0 2.0G 0% /dev/shm
/dev/sda5 20G 8.3G 11G 45% /home
/dev/sda9 2.0G 36M 1.9G 2% /log
/dev/sda7 9.9G 789M 8.6G 9% /opt
/dev/sda6 9.9G 94M 9.3G 1% /tmp
/dev/sda2 50G 12G 36G 25% /usr
/dev/sda3 30G 521M 28G 2% /var

df -k:

[oracle@adcpblr LogicalBackup]$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda10 153154336 92802068 52572392 64% /-------------------52572392kb--52572Mb--52Gb
/dev/sda1 497829 17200 454927 4% /boot
none 2072328 0 2072328 0% /dev/shm
/dev/sda5 20641788 8678432 10914716 45% /home
/dev/sda9 2063504 35880 1922804 2% /log
/dev/sda7 10317828 807792 8985920 9% /opt
/dev/sda6 10317828 96240 9697472 1% /tmp
/dev/sda2 51605464 12095596 36888464 25% /usr
/dev/sda3 30961696 533120 28855816 2% /var

Monitoring Services of a Listener

The SERVICES command of the Listener Control utility provides detailed information about the services and instances registered with a listener and the service handlers allocated to each instance.
The SERVICES command generates output with the sections described in Table 10-3.

Table 10-3 Listener Control Utility SERVICES Command

Output Section	Description
`Service`	Identifies the registered service
`Instance`	Specifies the name of the instance associated with the service
	The status field indicates if the instance is able to accept connections. · A `READY` status means that the instance can accept connections. · A `BLOCKED` status means that the instance cannot accept connections. · A `READY/SECONDARY` status means that the is a secondary instance in an Oracle Real Application Clusters primary/secondary configuration and is ready to accept connections. · A `RESTRICTED` status means that the instance is in restricted mode. The listener blocks all connections to this instance. · An `UNKNOWN` status means that the instance is registered statically in the `listener.ora` file rather than dynamically with service registration. Therefore, the status is non known.
`Handlers`	Identifies the name of the service handler. Dispatchers are named `D000` through `D999`. Dedicated servers have a name of `DEDICATED`. This section also identifies the following about the service handler: · `established`: The number of client connections this service handler has established · `refused`: The number of client connections it has refused · `current`: The number of client connections it is handling, that is, its current load · `max`: The maximum number of connections for the service handler, that is, its maximum load · `state`: The state of the handler: - A `READY` state means that the service handler can accept new connections. - A `BLOCKED` state means that the service handler cannot accept new connections. Following this, additional information about the service handler displays, such as whether the service handler is a dispatcher, a local dedicated server, or a remote dedicated server on another node.

How to grant on v$ views

SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
I wonder why i couldn’t give select privilage to a v$ view. I tried to give to permission to another v$ views but the error was same. Action for this error on error codes manual was meaningless to me (Action: You may only select rows from fixed tables/views.)
From a little googling effort i saw that the problem is caused because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must give it like below
SQL> grant select on v_$session to hr;

Grant succeeded.