Tag Archives: tablespace

11g, hp-ux, linux, Oracle 10g, oracle 9i, unix

Oracle Error ORA-25153

Leave a comment

Oracle Error ORA-25153 is a regular occurrence during the modification of location or recreation of an Oracle database. It indicates that the administrator neglected to add datafiles to a temporary tablespace. This article provides a solution the error ORA-25153, if it occurs.

Typical Error message:

Errors in file /u01/app/oracle/product/10.2.0/db_1/admin/orcl/bdump/orcl_j001_25535.trc:
ORA-25153: Temporary Tablespace is Empty
Sun Sep 19 01:31:00 2010
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /u05/oradata/orcl/group_3.dbf

Solution:

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Sep 19 08:08:39 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Add a temp file to your default temporary tablespaces with the following syntax: alter tablespace add tempfile < 'directory and file name'> size ;

example:


SQL> alter tablespace temp add tempfile ‘/u05/oradata/orcl/temp01.dbf’ size 2048m;

Tablespace altered.

SQL>

4. This completes the resolving of Oracle error ORA-25153.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreation of Oracle tablespace

Leave a comment

Oracle tablespaces provide storage of permanent and temporary data within the an Oracle database. The two forms of Oracle tablespaces are permanent which is used to store data which can survive restart of the database and temporary which is used for sort and other temporary storage requirements and does not require survival on restart of the database. In this article we will detail a simple script to recreation of the DDL necessary to transport a tablespace structure between databases. Feel free to modify the script to allow for more complex tablespace definitions.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 22 13:30:13 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create a permanent tablespace to demonstrate the recreation of your DDL with the following statements.

create tablespace test_data datafile ‘/u02/ORADATA/ORCL10G/test_data01.dbf’ size 1m, ‘/u02/ORADATA/ORCL10G/TEST_data02.dbf’ size 1m;

3. Execute the following PL\SQL block to reproduce the DDL for a tablespace defined by the variable V_TS_NM.

set serveroutput on

spool ./create_tablespace.lst

declare

v_ct number:=0;
v_ct_files number;
v_ts_nm varchar2(30) := ‘TEST_DATA’;

begin

dbms_output.put_line(‘create tablespace ‘||v_ts_nm||’ datafile ‘);

select count(*) into v_ct_files from dba_data_files where tablespace_name=v_ts_nm;

for v_file in(select file_name, bytes/1024/1024 as v_size from dba_data_files
where tablespace_name=v_ts_nm)
loop

dbms_output.put_line(””||v_file.file_name||””||’ size ‘||v_file.v_size||’m’);

v_ct:=v_ct+1;

if v_ct

4. The statements produced can now be executed against another database to recreate this tablespace structure. NOTE: The directory structure on the destination server may be different from this system, so edit the physical structure of the datafiles if necessary.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, RMAN

RMAN recovery of lost or corrupted tablespace datafile

Leave a comment

Oracle’s Recovery Manger (RMAN), originally released in Oracle 8, is a proprietary utility used for the backup and recovery of Oracle databases. Given a good Oracle RMAN backup, A DBA can recover from any disaster which may occur to their database, to include: loss of controlfiles, loss of datafiles, corruption of datafiles, loss of the entire database and even provides the ability to clone a database. In this article we give detailed instructions on the recovery of a lost or corrupted tablespace datafile in Oracle 9i, however these instructions will work in versions 10g and 11g.

1. Logon to the oracle server as the oracle software owner.

2. Set the ORACLE_SID and logon to sqlplus as sysdba.

[oracle@testdb02 scripts]$ su – oracle
Password:
[oracle@testdb02 ~]$ export ORACLE_SID=ORCL
[oracle@testdb02 ~]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:03:06 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL>

3. Query the dba_data_files table for tablespaces and datafiles. We are looking for a datafile which can be moved out of the location specified in the controlfile, resulting in tablespace corruption

SQL> select tablespace_name, file_name from dba_data_files order by tablespace_name;
TABLESPACE_NA FILE_NAME
————- ————————————————–
SYSTEM /u01/oradata/ORCL/system01.dbf
TOOLS /u05/oradata/ORCL/tools01.dbf
UNDOTBS1 /u04/oradata/ORCL/undo01.dbf
USERS /u05/oradata/ORCL/users01.dbf

SQL>

4. Shutdown the database with immediate.

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

5. Exit SQL*PLUS and move to directory /u05/oradata/ORCL. Create a temp directory and move the datafile users01.dbf into the new temp directory.

[oracle@testdb02 ~]$ cd /u05/oradata/ORCL
[oracle@testdb02 ORCL]$ ls
temp02.dbf tools01.dbf users01.dbf
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mkdir temp
[oracle@testdb02 ORCL]$
[oracle@testdb02 ORCL]$ mv users01.dbf temp/
[oracle@testdb02 ORCL]$ ls
temp temp02.dbf tools01.dbf
[oracle@testdb02 ORCL]$

6. Log back into sqlplus and startup your Oracle instance. Note: the error received referring to datafile users01.dbf, there exists a reference to this datafile in the controlfile, however Oracle could not locate the file. This results in the tablespace USERS being in an unusable inconsistent state.

[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 20:48:14 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 144670496 bytes
Fixed Size 741152 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 1323008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘/u05/oradata/ORCL/users01.dbf’

SQL>

7. To resolve the inconsistence, we must startup the RMAN utility and connect to both the target database and recovery catalog.

[oracle@testdb02 ORCL]$ rman

Recovery Manager: Release 9.2.0.8.0 – 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/h1g0alz

connected to target database: ORCL (DBID=1373796530)

RMAN> connect catalog rman/rman@RMANDB

connected to recovery catalog database

RMAN>

8. From within the RMAN utility, issue the command to ‘restore tablespace ’. In this example the tablespace was USERS. The restore command replaces the tablespace’s datafiles in your database with the copy in your backup set.

RMAN> restore tablespace users;

Starting restore at 12-JUN-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u05/oradata/ORCL/users01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u07/RMAN_BACKUP/ORCL/ORCL_657220129_1_26_1_0qjiooh1.dbf tag=BACKUP_FULL_ORCL params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-JUN-08

RMAN>

9. From within the RMAN utility, issue the command ‘recover tablespace ’. The recover command applies all archive redo logs which have occurred since the last backup set.

RMAN> recover tablespace users;

Starting recover at 12-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK

starting media recovery

archive log thread 1 sequence 28 is already on disk as file /u01/oradata/ORCL/archive/ORCL_ARCH28.arc
archive log thread 1 sequence 29 is already on disk as file /u06/oradata/ORCL/archive/ORCL_ARCH29.arc
archive log filename=/u01/oradata/ORCL/archive/ORCL_ARCH28.arc thread=1 sequence=28
media recovery complete
Finished recover at 12-JUN-08

RMAN>

10. Exit the RMAN utility and enter SQL*PLUS, alter the database to an open status. You know that the tablespace USERS is now in a consistent state with the controlfiles when the database opens successfully

RMAN> exit

Recovery Manager complete.
[oracle@testdb02 ORCL]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 9.2.0.8.0 – Production on Thu Jun 12 21:03:11 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production

SQL> alter database open;

Database altered.

SQL>

This completes successful recovery of a tablespace.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Oracle Temporary Tablespace Movement

Leave a comment

Temporary tablespace’s in the Oracle environment are used for sorting and other non-permanent data operations. Although the space in a temporary tablespace is not permanent, a database of any size cannot properly function without the use of very large amounts of temporary tablespace. This procedure covers the movement of a temporary tablespace after space on the original storage device becomes insufficient. We will cover the movement of the TEMPORARY tablespace from the mount point data1 to mount point data3.

1. Find all tablespaces defined as TEMPORARY

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’;

TABLESPACE_NAME
——————————
TEMP
TEMPORARY

SQL>

2. Locate the temp_files in the temporary tablespace.

SQL> select file_name from dba_temp_files where tablespace_name = ‘TEMPORARY’;

FILE_NAME
/data1/temporary01.dbf
/data1/temporary02.dbf
/data1/temporary03.dbf

SQL>

3. Find size of current files

SQL> select file_name, bytes/1024/1024 from dba_temp_files where tablespace_name = ‘TEMPORARY’;
FILE_NAME BYTES/1024/1024
—————— —————
/data1/temporary01.dbf 4096
/data1/temporary02.dbf 2048
/data1/temporary03.dbf 2048

SQL>

4. Add new temp files on your new mount point to the tablespace temporary with the command ‘alter tablespace’.

SQL> alter tablespace temporary add tempfile ‘/data3/temporary01.dbf’
size 4096m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary02.dbf’
size 2048m;
Tablespace altered.

SQL> alter tablespace temp add tempfile ‘/data3/temprary03.dbf’
size 2048m;
Tablespace altered.

5. Shutdown database to free all in-use segments of the temporary tablespace TEMPORARY with the command ‘shutdown immediate’.

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

6. Startup the database in restricted mode using the command ‘startup restict’ so no user activity can pickup segments from the tablespace TEMPORARY.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

7. Drop the old datafiles using the command ‘alter database’

SQL> alter database tempfile ‘/data1/temprary01.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary02.dbf’
drop including datafiles;
Database altered.

SQL> alter database tempfile ‘/data1/temprary03.dbf’
drop including datafiles;
Database altered.

8. View the new temp files with the following command.

SQL> select file_name from dba_temp_files where tablespace_name=’TEMPORARY’;
FILE_NAME
———————–
/data3/temprary01.dbf
/data3/temprary02.dbf
/data3/temprary03.dbf

SQL>

9. Open the database for normal use with ‘alter system’ command.

SQL> alter system disable restricted session;

System altered.

SQL>

This completes the movement of temporary tablespaces tempfiles.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g

Movement of Oracle datafiles

Leave a comment

Oracle allows for the movement of datafiles from one storage device to another when you exhaust space on the original device without having to recreate your database. In this procedure we will move the datafiles located on LUN data4 to a new LUN on data1.

1. Find all datafiles located on mount point /data4 and view size in MB.

SQL> select tablespace_name, bytes/1024/1024, file_name from dba_data_files where
file_name like ‘%data3%’;

SYSTEM /data4/oradata/system01.dbf
DRSYS /data4/oradata/drsys01.dbf
TOOLS /data4/oradata/tools01.dbf
OEM /data4/oradata/oem01.dbf
USERDATA /data4/oradata/userdata_01.dbf
USERDATA /data4/oradata/userdata_02.dbf
USERDATA /data4/oradata/userdata_03.dbf
USERDATA /data4/oradata/userdata_04.dbf

SQL> select SUM(bytes)/1024/1024 from dba_data_files
13:15:46 2 where file_name like ‘%data4%’;

SUM(BYTES)/1024/1024
——————–
32546

2. The data files in tablespace USERDATA amount to 32GB of space,
we will move these data files to the new mount point /data1.

3. Shutdown the oracle database with the command ‘shutdown immediate’.

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

4. Startup the oracle database in restricted mode, this will prevent non-DBA users from accessing the database while you moving the data files. Use the command ‘startup restrict’.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

5. Take the tablespace USERDATA offline.

SQL> alter tablespace USERDATA offline;

Tablespace altered.

SQL>

6. View the status of the tablespace USERDATA to ensure that it is in an offline state.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name =
‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA OFFLINE

SQL>

7. Shutdown the database with the command ‘shutdown immediate’.

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

8. Move the data files of tablespace USERDATA to their new locations with OS command ‘cp’. One could use the command mv, which is normally faster, however if anything goes wrong you do not have a backup of your datafile.

cp /data4/oradata/userdata_01.dbf /data1/oradata/userdata_01.dbf
cp /data4/oradata/userdata_02.dbf /data1/oradata/userdata_02.dbf
cp /data4/oradata/userdata_03.dbf /data1/oradata/userdata_03.dbf
cp /data4/oradata/userdata_04.dbf /data1/oradata/userdata_04.dbf

9. Open the database in a nomount state with ‘startup nomount’.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 155192196 bytes
Database Buffers 448790528 bytes
Redo Buffers 7135232 bytes
SQL>

10. Change the location of the data files moved in the control file with the command ‘alter database rename datafile’.

SQL> alter database rename datafile ‘/data4/oradata/userdata_01.dbf’ to
‘/data1/oradata/userdata_01.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_02.dbf’ to
‘/data1/oradata/userdata_02.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_03.dbf’ to
‘/data1/oradata/userdata_03.dbf’;
Database alerted.

SQL> alter database rename datafile ‘/data4/oradata/userdata_04.dbf’ to
‘/data1/oradata/userdata_04.dbf’;
Database alerted.

11. Mount the database with the command ‘alter database mount’.

SQL> alter database mount;

Database altered.

SQL>

12. Open the database with command ‘alter database open’.

SQL> alter database open;

Database altered.

SQL>

13. Bring the tablespaces back online.

SQL> alter tablespace USERDATA online;

Tablespace altered.

SQL>

14. Validate that the tablespace is online with the following SQL.

SQL> select tablespace_name, status from dba_tablespaces where tablespace_name
2 ‘USERDATA’;

TABLESPACE_NAME STATUS
—————————— ———
USERDATA ONLINE

SQL>

Completes movement of Oracle data files.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com