Feeds:
Posts
Comments

Posts Tagged ‘tempfile’

Dear all, yesterday I face small issue which I would like to share with you guy’s.It was temporary tablespace issue.This was development server for storage was recently changed.As mount point was changed but control file was having the previous temp file details so got the error while exporting schema.Just drop the old tempfile and created new one. Issue got resolved after that.

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 reporter_sam_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 reporter_sam_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:13:42 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > SAM1_orig_exp31052011.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

Export grants (yes/no): yes > y

Export table data (yes/no): yes > y

Compress extents (yes/no): yes > y

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users …

User to be exported: (RETURN to quit) > SAM1

User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export SAM1’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

EXP-00056: ORACLE error 1157 encountered

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

EXP-00000: Export terminated unsuccessfully

bash-2.05$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jun 1 04:16:46 2011

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

 Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

 SQL> select * from v$tempfile;

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

———- —————- ——— ———- ———- ——- ———-

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

———- ———- ———— ———-

NAME

——————————————————————————–

         1       8.2892E+12 07-FEB-11         24          2 ONLINE  READ WRITE

         0          0   2147483648       8192

/oradata/oracle10g/oradata/temp/temp2.dbf

          3       8.2892E+12 07-FEB-11         24          1 ONLINE  READ WRITE

         0          0   3221225472       8192

/oradata/oracle10g/oradata/temp/temp1.dbf

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

———- —————- ——— ———- ———- ——- ———-

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

———- ———- ———— ———-

NAME

——————————————————————————–

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                          *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

          3 ONLINE

/oradata/oracle10g/oradata/temp/temp1.dbf

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp1.dbf

/oradata/oracle10g/oradata/temp/temp1.dbf: No such file or directory

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp2.dbf

/oradata/oracle10g/oradata/temp/temp2.dbf: No such file or directory

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                           *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp1.dbf’ drop;

 Database altered.

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp1.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

ERROR:

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp2.dbf’ drop;

 Database altered.

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp2.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

 TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

        MB

———-

TEMP1

/DATA1/temp1.dbf

     10240

 TEMP1

/DATA1/temp2.dbf

     10240

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/DATA1/temp2.dbf

          2 ONLINE

/DATA1/temp1.dbf

 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

bash-2.05$ pwd

/DATA1

bash-2.05$ cd –

/DATA

bash-2.05$ cd sam_cxl

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 sam_cxl_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 sam_cxl_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

 Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:35:18 2011

 Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 Username: / as sysdba

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

 Export file: expdat.dmp > SAm1_orig_exp31052011.dmp

 (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

 Export grants (yes/no): yes >

 Export table data (yes/no): yes >

 Compress extents (yes/no): yes >

 Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

 About to export specified users …

User to be exported: (RETURN to quit) > SAM1

 User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export ISCM’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SAM1’s tables via Conventional Path …

. . exporting table       SAM_BERTHING_GRADE_ARMS          5 rows exported

.

.

.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

Hope you guy’s enjoy this post.Very soon I wil come up with Parsing & execution as well.

Experts are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »