MRP0: Background Media Recovery terminated with error 1111

Today while troubleshooting a DR database sync scenario issue, I got below error in alert log file :-

MRP0: Background Media Recovery terminated with error 1111
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit1_pr00_1890.trc:
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'
ORA-01157: cannot identify/lock data file 34 - see DBWR trace file
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'

Cause :-

Above issue can occur in DR database in below scenarios :-

1. Space issue in DR database and a datafile is added in primary database.
2. Standby file management is set to manual
3. DB_create_file_dest or DB_file_name_convert parameter is not set in Standby database

Troubleshoot :-

When I crosscheck the primary database and standby database sync details , I saw Redo Apply was stopped.

I crosschecked the alert log files for standby database and got above mentioned error in alert logfile.

Checked the datafile details in DR database :-

File_id       name
34             /opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

Checked the Primary alert log file and got to know that a datafile addition activity happened in primary database.

Crosschecked all the above mentioned parameters and got to know that space exhausted in BCP database due to that Oracle created an Unknown file in ORACLE_HOME location due to which Redo Apply stopped.

Solution :-

Checked the added file detail in Production database :-

File_id       name
34            +DATA/amit/datafile/data.321.931222251

Login to Standby database server :-

1. As space was exhausted on Diskgroup level, added space by logging in to ASM instance
2. Login to DR database instance :-

a. Change the standby_file_management parameter to Manual

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO
SQL> alter system set standby_file_management=MANUAL sid='*';

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL>
SQL> select name from v$datafile where file#=34;

NAME
--------------------------------------------------------------------------------
/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

SQL> alter database create datafile '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034' as '+DATA' size 10g;

Database altered.

After creation of the datafile in DR database , check the file detail in DR database :-

File_id       name
34             +DATA/amit_dr/datafile/data.278.293456881

Check the redo apply status, if not started , start it manually using dgmgrl.

I hope this article helped you.

Thanks
Amit Rath

Cross Platform Transportable Tablespace using RMAN in Oracle Database

Cross Platform Transportable Tablespace with Different Endianness

Reference data for this Post:-
1. Tablespace to be moved : TEST
2. Source Operating system :- Solaris[tm] OE (64-bit) 
3. Target Operating system :- Linux x86 64-bit
4. Datafiles residing in :- ASM
5. Source Database version :- 11.2.0.2
6. Target Database Version :- 11.2.0.4

Steps to perform Cross platform Transportable Tablespace on Source Database:-

1. Pre Check the data for the tablespace to be moved(optional)

SQL> select table_name from user_tables;

TABLE_NAME         OWNER
--------------------        ----------------
AMIT_1 AMIT
AMIT_2 AMIT
AMIT_3 AMIT

SQL> select count(1) from AMIT_2;

  COUNT(1)
----------
  11496960
  
SQL> select distinct tablespace_name from dba_segments where owner='AMIT';

TABLESPACE_NAME
------------------------------
TEST

2. Check whether both Platform have different Endianness 

Check the target operating system detail from below query:-

Source :-

select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                                                                         ENDIAN_FORMAT
--------- ----------------------------------------------------------------------------------------------------- --------------
AMIT     Solaris[tm] OE (64-bit)                                                                                      Big

Target :-

select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                                                                         ENDIAN_FORMAT
--------- ----------------------------------------------------------------------------------------------------- --------------
TEST     Linux x86 64-bit                                                                                           Little

If yes , then we have to convert the endianness of datafile as per target database.

3. Change the Tablespace to Read Only mode 

SQL> alter tablespace test read only;

Tablespace altered.

4. Check whether tablespace can be transported without any errors

SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected

5. Restrictions for Cross platform transportable tablespace

a. Both Source and target should use the same Characterset and National Character set

b. Tablespace has to be Self Contained means Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set

c. Compatibility must be greater than 10.0 for both

6. Export the metadata of tablespace using datapump

$ expdp directory=DP_DUMP dumpfile=expdp_transport1.dmp logfile=expdp.log transport_tablespaces=test exclude=xmlschema

Export: Release 11.2.0.2.0 - Production on Sat Jul 11 12:06:42 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=DP_DUMP dumpfile=expdp_transport1.dmp logfile=expdp.log transport_tablespaces=test exclude=xmlschema

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:

  /u00/oracle/admin/AMIT/dpdump/expdp_transport1.dmp

******************************************************************************

Datafiles required for transportable tablespace TEST:

  +DATADG/AMIT/datafile/test.349.884746017

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:06:55

7. Convert the datafile endianness as per the target operating system .

As we see in Step 2 that both Source and target have different endianness, we have convert the endianness of datafile as per target

Start the conversion :-

$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jul 11 12:09:47 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: AMIT (DBID=387143729)

RMAN> convert tablespace "TEST" to platform 'Linux x86 64-bit' FORMAT='/ora_backup/dpdump/AMIT/%U';

Starting conversion at source at 2015-07-11 12:10:08

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=112 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00045 name=+DATADG/AMIT/datafile/test.349.884746017

converted datafile=/ora_backup/dpdump/AMIT/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55

Finished conversion at source at 2015-07-11 12:11:04

8. Once conversion completed, SCP the files to target platform( Converted datafile and Dumpfile for metadata of tablespace)

$ scp data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1 server1:/ora_backup/test/restore_test/amit/backup

$ scp expdp_transport1.dmp server1:/ora_backup/test/restore_test/amit/backup

9. Change the tablespace to Read Write mode

SQL> alter tablespace test read write;

Tablespace altered.

Steps to perform Cross platform Transportable Tablespace on Target Database:-

1. Check the Version of target database(optional)

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

2. Convert the datafile as per the target Database file details 

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 11 14:20:12 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST1 (DBID=1261817935)

RMAN> convert datafile '/ora_backup/test/restore_test/amit/backup/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1' db_file_name_convert='/ora_backup/test/restore_test/amit/backup','+DATA1_test/test1/datafile/';

Starting conversion at target at 2015-07-11 14:20:55

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=235 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input file name=/ora_backup/test/restore_test/amit/backup/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1

converted datafile=+DATA1_test/test1/datafile/data_d-AMIT_i-387143729_ts-test_fno-45_02qbpap1

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15

Finished conversion at target at 2015-07-11 14:21:11

3. Create the appropriate Users and roles as they were present in Source database

Create user Amit identified by *****;

Grant login, schema to amit;

If this tablespace is to be associated to different user then remap_schema has to be used while importing the metadata.

4. Import the metadata of the tablespace in target Database 

$ impdp directory=datapump dumpfile=expdp_transport1.dmp logfile=impdp1.log transport_datafiles=+DATA1_test/test1/datafile/TEST.348.884787657

Import: Release 11.2.0.4.0 - Production on Sat Jul 11 14:23:53 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Source time zone is -05:00 and target time zone is +00:00.

Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=datapump dumpfile=expdp_transport1.dmp logfile=impdp1.log transport_datafiles=+DATA1_test/test1/datafile/TEST.348.884787657

Processing object type TRANSPE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORORTABLT/TABLE

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Jul 11 14:23:59 2015 elapsed 0 00:00:03

5. Check the new datafile details in target database 

SQL> select name from v$datafile;

NAME

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

+DATA1_test/test1/datafile/system.365.884775499

+DATA1_test/test1/datafile/sysaux.326.884775501

+DATA1_test/test1/datafile/undotbs1.380.884775501

+DATA1_test/test1/datafile/users.381.884775501

+DATA1_test/test1/datafile/test.348.884787657

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

TEST

6 rows selected.

6. Make the tablespace read write in Target database 

SQL> alter tablespace test read write;

Tablespace altered.

7. Check the data associated with tablespace in target and compare it with source for verification(Optional)

SQL> conn amit/Passw0rd

Connected.

SQL> select table_name from user_tables;

TABLE_NAME

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

AMIT_2

AMIT_3

AMIT_1

SQL> select count(1) from AMIT_2;

  COUNT(1)

----------

  11496960

Cross Platform Transportable Tablespace with different Endianness completed.

I hope this article helped you.

Thanks

Amit Rath

How to perform Tablespace point in time recovery (TSPITR)

TSPITR abbreviates to Tablespace point in time recovery. Unlike DBPITR , it rewinds only specified tablespace to a earlier time and leaving the whole database in the current time. All changes made to the tablespace after the recovery time will be lost.

While doing TSPITR you have to be very cautious if you are doing it using control file because once you recovered your tablespace to a earlier time and its not fulfilled your requirement then there is no second chance for that. All backups related to that tablespace becomes invalid after TSPITR if using control file instead of recovery catalog. 

PFB Example to recover a table using Fully Automated TSPITR after truncate operation :-

Prerequisites :-

1.  If I want recovery to a particular time then I should have a Valid Backup available before that time to perform TSPITR.

2. Database has to be in Archive Log mode. 

C:\Users\NewAdmin>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 6 23:43:01 2013

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

Enter user-name: /as sysdba

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

SQL> conn amit/amit
Connected.
SQL> create table amit
  2  (
  3  id number(12),
  4  name varchar2(12));

Table created.

SQL> insert into amit values ('&id','&name');
Enter value for id: 3242
Enter value for name: ffsdf
old   1: insert into amit values ('&id','&name')
new   1: insert into amit values ('3242','ffsdf')

1 row created.
.........

like this insert some rows in table

SQL> commit;

Commit complete.

SQL> select * from amit;

        ID NAME
---------- ------------
      3242 ffsdf
      4324 fdfdf
    324324 ssdfdsf
     43242 fsfdf/
     43242 ffewtew
   4324234 fsfegg

6 rows selected.

SQL> alter system switch logfile;

System altered.
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-AUG-13 11.46.38.945000 PM +05:30

SQL> truncate table amit;

Table truncated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-AUG-13 11.47.05.185000 PM +05:30

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\NewAdmin>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 6 23:47:21 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2120650031)

RMAN> recover tablespace amit until time "to_date('06-AUG-13 23:46:38','dd-mon-yy hh24:mi:ss')" auxiliary destination 'D:\backup';

Starting recover at 06-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=198 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='ADAF'

initialization parameters used for automatic instance:
db_name=TEST
db_unique_name=ADAF_tspitr_TEST
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=D:\backup
log_archive_dest_1='location=D:\backup'
#No auxiliary parameter file used

starting up automatic instance TEST

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2175128 bytes
Variable Size                100667240 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4886528 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('06-AUG-13 23:46:38','dd-mon-yy hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 06-AUG-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=81 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2013_08_06\O1_MF_S_822784043_902F2NHM_.BKP
channel ORA_AUX_DISK_1: piece handle=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2013_08_06\O1_MF_S_822784043_902F2NHM_.BKP tag=TAG20130806T230723
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\BACKUP\TEST\CONTROLFILE\O1_MF_902HTHRJ_.CTL
Finished restore at 06-AUG-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('06-AUG-13 23:46:38','dd-mon-yy hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'AMIT' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to
 "G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\AMIT01.DBF";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace AMIT offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to D:\BACKUP\TEST\DATAFILE\O1_MF_TEMP_%U_.TMP in control file

Starting restore at 06-AUG-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to D:\BACKUP\TEST\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to D:\BACKUP\TEST\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to D:\BACKUP\TEST\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\AMIT01.DBF
channel ORA_AUX_DISK_1: reading from backup piece G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_08_06\O1_MF_NNNDF_TAG20130806T230534_902DZ6YP_.BKP
channel ORA_AUX_DISK_1: piece handle=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_08_06\O1_MF_NNNDF_TAG20130806T230534_902DZ6YP_.BKP tag=TAG20130806T23053
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 06-AUG-13

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=822787012 file name=D:\BACKUP\TEST\DATAFILE\O1_MF_SYSTEM_902HTTV8_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=822787012 file name=D:\BACKUP\TEST\DATAFILE\O1_MF_UNDOTBS1_902HTV2W_.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=822787012 file name=D:\BACKUP\TEST\DATAFILE\O1_MF_SYSAUX_902HTTX3_.DBF

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('06-AUG-13 23:46:38','dd-mon-yy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "AMIT", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 06-AUG-13
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_12_902F2JG8_.ARC
archived log for thread 1 with sequence 13 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_13_902HC6KM_.ARC
archived log for thread 1 with sequence 14 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_14_902HC88F_.ARC
archived log for thread 1 with sequence 15 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_15_902HCFM2_.ARC
archived log for thread 1 with sequence 16 is already on disk as file G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_16_902HDWNH_.ARC
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_12_902F2JG8_.ARC thread=1 sequence=12
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_13_902HC6KM_.ARC thread=1 sequence=13
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_14_902HC88F_.ARC thread=1 sequence=14
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_15_902HCFM2_.ARC thread=1 sequence=15
archived log file name=G:\APP\NEWADMIN\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2013_08_06\O1_MF_1_16_902HDWNH_.ARC thread=1 sequence=16
media recovery complete, elapsed time: 00:00:06
Finished recover at 06-AUG-13

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  AMIT read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
D:\backup''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
D:\backup''";
}
executing Memory Script

sql statement: alter tablespace  AMIT read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backup''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''D:\backup''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_ADAF":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_ADAF" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_ADAF is:
   EXPDP>   D:\BACKUP\TSPITR_ADAF_54021.DMP
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace AMIT:
   EXPDP>   G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\AMIT01.DBF
   EXPDP> Job "SYS"."TSPITR_EXP_ADAF" successfully completed at 23:59:45
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  AMIT including contents keep datafiles';
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

sql statement: drop tablespace  AMIT including contents keep datafiles

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_ADAF" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_ADAF":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_ADAF" successfully completed at 00:00:17
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  AMIT read write';
sql 'alter tablespace  AMIT offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  AMIT read write

sql statement: alter tablespace  AMIT offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file D:\BACKUP\TEST\DATAFILE\O1_MF_TEMP_902J05F7_.TMP deleted
auxiliary instance file D:\BACKUP\TEST\ONLINELOG\O1_MF_3_902HZY45_.LOG deleted
auxiliary instance file D:\BACKUP\TEST\ONLINELOG\O1_MF_2_902HZVTN_.LOG deleted
auxiliary instance file D:\BACKUP\TEST\ONLINELOG\O1_MF_1_902HZSHD_.LOG deleted
auxiliary instance file D:\BACKUP\TEST\DATAFILE\O1_MF_SYSAUX_902HTTX3_.DBF deleted
auxiliary instance file D:\BACKUP\TEST\DATAFILE\O1_MF_UNDOTBS1_902HTV2W_.DBF deleted
auxiliary instance file D:\BACKUP\TEST\DATAFILE\O1_MF_SYSTEM_902HTTV8_.DBF deleted
auxiliary instance file D:\BACKUP\TEST\CONTROLFILE\O1_MF_902HTHRJ_.CTL deleted
Finished recover at 07-AUG-13

RMAN> exit

Recovery Manager complete.

C:\Users\NewAdmin>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 7 00:05:18 2013

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

Enter user-name: /as sysdba

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

SQL> conn amit/amit
Connected.

SQL> select count(1) from amit;
select count(1) from amit
                     *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: 'G:\APP\NEWADMIN\ORADATA\TEST\DATAFILE\AMIT01.DBF'

##############AFTER TSPITR you have to manually make your tablespace online###############

SQL> alter tablespace amit online;

Tablespace altered.

SQL> select * from amit;

        ID NAME
---------- ------------
      3242 ffsdf
      4324 fdfdf
    324324 ssdfdsf
     43242 fsfdf/
     43242 ffewtew
   4324234 fsfegg

6 rows selected.

Table has been successfully recovered using TSPITR. 

Please note that if you have hands on in using TSPITR only then use it for recovery in Production database.

If possible Always preferred Flashback feature ahead of TSPITR.

Related Articles :-
How to perform DBPITR

I hope this article helped you.

Regards,
Amit Rath

Cross platform transportable tablespaces in oracle 11g

Cross platform transportable tablespace is a term which is used when we have to transport tablespaces between databases having different endian formats between source and destination platforms. Endian Formats of the datafiles must be converted to match the destination platforms.

This conversion can be performed in two ways :-
1. RMAN convert tablespace , converting on source host.
2. RMAN convert datafile , converting on target host.

This feature is compatible from Oracle Database Release 10.1 and above.

Limitations :-

1. Source Database and Target Database must have same Character set and National Character set.
2. Target database must not have tablespace with same name which has to be transported from Source.
3. Target database version must have to be same or higher than source database version. (exporting using parameter VERSION in expdp does not work here)

PFB steps to transport a tablespace  :-

1. Conversion to be done at source DB from AIX 64 bit system to Microsoft Windows IA (32-bit) :-

 a. Operating system detail on source and target system :-

SQL > select  * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME                                 ENDIAN_FORMAT
----------- --------------------------------------------- ------------------------------------------
          1 Solaris[tm] OE (32-bit)                       Big
          2 Solaris[tm] OE (64-bit)                       Big
          7 Microsoft Windows IA (32-bit)                 Little
         10 Linux IA (32-bit)                             Little
          6 AIX-Based Systems (64-bit)                    Big
          3 HP-UX (64-bit)                                Big
          5 HP Tru64 UNIX                                 Little
          4 HP-UX IA (64-bit)                             Big
         11 Linux IA (64-bit)                             Little
         15 HP Open VMS                                   Little
          8 Microsoft Windows IA (64-bit)                 Little

PLATFORM_ID PLATFORM_NAME                                 ENDIAN_FORMAT
----------- --------------------------------------------- ------------------------------------------
          9 IBM zSeries Based Linux                       Big
         13 Linux x86 64-bit                              Little
         16 Apple Mac OS                                  Big
         12 Microsoft Windows x86 64-bit                  Little
         17 Solaris Operating System (x86)                Little
         18 IBM Power Based Linux                         Big
         19 HP IA Open VMS                                Little
         20 Solaris Operating System (x86-64)             Little
         21 Apple Mac OS (x86-64)                         Little

b. Check that tablespace which has to be transported contains any refrential objects or dependencies.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('AMIT', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

c. Make the tablespace read only.

SQL> alter tablespace amit read only;

Tablespace altered.

d. Connect to RMAN at source DB and convert tablespace AMIT

bash-3.2$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 3 15:38:44 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDR (DBID=903650087)

RMAN> convert tablespace amit to platform 'Microsoft Windows IA (32-bit)' format ='/backup/%U';

Starting conversion at source at 03-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3237 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00033 name=/disk1/oracle/oradata/amit.dbf
converted datafile=/backup/data_D-CDR_I-903650087_TS-AMIT_FNO-33_6aodqt3b
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 03-JUL-13

e. Export Metadata of the tablespace

bash-3.2$ expdp system/system directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces = AMIT &
[1] 50855988

Export: Release 11.2.0.1.0 - Production on Wed Jul 3 12:34:57 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
bash-3.2$
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces=AMIT
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /disk1/daapump/transportable_tablespace.dmp
******************************************************************************
Datafiles required for transportable tablespace AMIT:
  /disk1/oracle/oradata/amit.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:35:35

[1]+  Done                    expdp system/system directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces = AMIT
bash-3.2$

f. Move the dumpfile and datafile to the target database where target database has permissions to access that

g. Plug the tablespace into target database by using IMPORT and RMAN

C:\Users\amit.rath\Downloads> rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 3 15:17:12 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1345007053)

RMAN> convert datafile 'C:\Users\amit.rath\Downloads\data_D-CDR_I-903650087_TS-AMIT_FNO-33_6aodqt3b' format 'C:\app\amit.rath\oradata\orcl\amit.dbf' ;

Starting conversion at target at 03-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=C:\USERS\AMIT.RATH\DOWNLOADS\DATA_D-CDR_I-903650087_TS-AMIT_FNO-33_6AODQT3B
converted datafile=C:\APP\AMIT.RATH\ORADATA\ORCL\AMIT.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 03-JUL-13

RMAN> exit

C:\Users\amit.rath\Downloads>impdp system/orcl directory=datapump dumpfile=transportable_tablespace.dmp transport_datafiles='c:\app\amit.rath\oradata\orcl\amit.dbf'

Import: Release 11.2.0.1.0 - Production on Wed Jul 3 15:17:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_datafiles='c:\app\amit.rath\oradata\orcl\amit.dbf'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 15:17:38

2. Conversion to be done at Target DB from Microsoft Windows IA (32-bit) to AIX 64 bit system :-

a. Check that tablespace which has to be transported contains any refrential objects or dependencies.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('AMIT', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

b. Make the tablespace read only.

SQL> alter tablespace amit read only;

Tablespace altered.

c. Export Metadata of the tablespace in source DB 

expdp system/system directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_tablespaces = EEEE 

Export: Release 11.2.0.1.0 - Production on Wed Jul 3 12:34:57 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=transportable_tablespace.dmp transport_tablespaces=AMIT
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\app\datapump\EXPDP_TRANS_EEEE.DMP
******************************************************************************
Datafiles required for transportable tablespace AMIT:
  C:\app\EEEE.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:35:35

d. Move the datafile and export dump to the target DB where it has permission to access those.

e. Convert the datafile using RMAN utility

RMAN> convert datafile '/disk2/oracle/EEEE.DBF' DB_FILE_NAME_CONVERT '/disk2/oracle/' , '/disk2/oracle/oradata/pacs' FROM PLATFORM 'Microsoft Windows IA (32-bit)' ;

Starting conversion at target at 05-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/disk2/oracle/EEEE.DBF
converted datafile=/disk2/oracle/oradata/pacsEEEE.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 05-JUL-13

f. Use the IMPDP utility to import the metadata of the tablespace

bash-3.2$ impdp system/system directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_datafiles= '/disk2/oracle/oradata/pacsEEEE.DBF'

Import: Release 11.2.0.1.0 - Production on Fri Jul 5 13:58:22 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=datapump dumpfile=EXPDP_TRANS_EEEE.DMP transport_datafiles=
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 13:58:25

g. check that the tablespace has been successfully added to that target DB 

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EEEEE

6 rows selected.

SQL> select segment_name,segment_type,owner from dba_segments where tablespace_name='EEEEE';

SEGMENT_NAME              SEGMENT_TYPE       OWNER
------------------------- ------------------ ------------------------------
AAAAA                     TABLE              AMIT

NOTE :- If you want to change the owner of the objects associated with the tablespace then while doing import you can use remap_schema parameter to do that.

I hope this article helped you.

Regards,
Amit Rath

How to move datafiles to another location

Sometimes a DBA might need to move database datafiles from one location to another. There can be various reasons for this movement :-

1. Change/Rectify incorrect naming of the datafiles.
2. I/O balancing on File systems due to performance reasons.

Datafiles can be moved to another location in two ways :-

1. Database is in online mode :-

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ WRITE

SQL> alter database datafile '/disk2/oracle/oradata/amit/amit/test.dbf' offline;

Database altered.


SQL> !
$ bash
bash-3.2$ cd /disk2/oracle/oradata/amit/amit/
bash-3.2$ mv test.dbf /disk1/oracle/oradata/amit/amit
bash-3.2$
bash-3.2$
bash-3.2$ exit
exit
$
$ exit

SQL> alter database rename file '/disk2/oracle/oradata/amit/amit/test.dbf' to '/disk1/oracle/oradata/amit/amit/test.dbf';

Database altered.

SQL> recover datafile '/disk1/oracle/oradata/amit/amit/test.dbf';
Media recovery complete.
SQL>
SQL> alter database datafile '/disk1/oracle/oradata/amit/amit/test.dbf' online;

Database altered.

Note :- IN online mode we cannot rename or move system datafiles. To do that PFB link :-
Renaming/Moving System datafiles

2. Database is in offline mode :-

SQL> select open_mode from v$database;

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

SQL> !

$ bash

bash-3.2$ cd /disk2/oracle/oradata/amit/amit/

bash-3.2$ mv test.dbf /disk1/oracle/oradata/amit/amit

bash-3.2$

bash-3.2$

bash-3.2$ exit

exit

$

$ exit

SQL> startup mount

ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.


SQL> alter database rename file '/disk2/oracle/oradata/amit/amit/test.dbf' to '/disk1/oracle/oradata/amit/amit/test.dbf';

Database altered.

SQL> alter database open;

Database altered.


I hope this article helped you.

Regards,
Amit Rath

Renaming SYSTEM datafiles of Oracle Database

System tablespace is the most critical tablespace in Oracle database. To rename datafiles related to SYSTEM tablespace , we cannot proceed as we proceed for normal ones. It has to be handled with care.

When we try to offline your system tablespace we will get below mentioned error :-

SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

Renaming datafiles of SYSTEM tablespace:-

1. Shut down the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down. 

2. Start the database in mount mode :-

SQL> startup mount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             226495364 bytes
Database Buffers          377487360 bytes
Redo Buffers                7135232 bytes
Database mounted.

3. Rename the system datafile of database:-
SQL> alter database rename  file 'C:\oracle\product\10.2.0\oradata\Amit\SYSTEM01.DBF' to 'C:\oracle\product\10.2.0\oradata\Amit\SYSTEM012.DBF' ;

Database altered.

4. Open the database :-

SQL> alter database open;

Database altered.

I hope this article helped you.

Regards,
Amit Rath

CREATE or ALTER a Tablespace

A tablespaces is made up of one or more database datafiles. Tablespaces are the logical components of a database , datafiles which it's made of are physical componets of database.

To alter a tablespace attributes there are some prerequesites :-

We need to have ALTER TABLESPACE system privilege to alter a tablespace. Having ALTER TABLESPACE system privilege we can perform any operation regarding tablespace, but if we have MANAGE TABLESPACE system privilege then we can only perform ONLINE/OFFLINE a tablespace, BEGIN/END a backup of tablespace, Make the tablespace readonly/readwrite.

To alter SYSAUX tablespace we need SYSDBA privilege.

How to create a tablespace :-

SQL> create tablespace test datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test01.dbf' size 100m;

SQL> create tablespace test datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test02.dbf' size 100m autoextend on;

How to rename a tablespace :-

SQL>  alter tablespace test1 rename to test2;

Renaming a datafile of a tablespace :-

To rename a datafile of a tablespace , we have to do that in three steps :-

1. Make the Tablespace Offline :- we cannot make a system tablespace offline, if necessary we have to shut down the database.

SQL> alter tablespace test offline normal;

2. Rename the datafile:-

SQL> alter tablespace test rename datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test01.dbf' to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test05.dbf'; 

3. Make the tablespace online :-

SQL> alter tablespace test online;

How to add a datafile to a Tablespace :-

SQL> alter tablespace test2 add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test03.dbf' size 100m;

How to drop a datafile of a tablespace :-

Datafile which you wants to drops has to be empty means no extent allocated to it.

SQL> alter tablespace test2 drop datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test03.dbf';

SQL> alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEMP01.DBF' drop including datafiles;

How to resize a datafile of a tablespace :-

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test02.dbf' resize 200m;

Details of tablespaces :-

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments group by tablespace_name;
                                                        OR

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_data_files group by tablespace_name;
                                                         OR 

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space group by tablespace_name;
                                                          OR

SQL> select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

Autoextend Parameter :-

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEST01.DBF' autoextend OFF;

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEST01.DBF' autoextend ON;

Logging Attribute :-

SQL> Alter tablespace TEST1 logging;

SQL> Alter tablespace TEST1 nologging;

How to DROP a tablespace :-

SQL> drop tablespace test including contents and datafiles;

It will drop the tablespace with data and datafiles.

SQL> drop tablespace amit including contents;

It will drop only tablespace and data from database but not that physical file from OS. we can again reuse that file to create a tablespace :-

SQL> create tablespace amit datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\amit01.dbf' size 1g reuse;

I hope this article helped you.

Regards,
Amit Rath

Temporary Tablespaces in Oracle Database

What are Temporary Tablespace

Temporary Tablespace contains transient data that persists only for the duration of the session.

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.

Note:- A temporary tablespace does not contain permanent objects and therefore doesn't need to be backed up.

How to create a temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

Tablespace created.

Default Temporary Tablespace

Default Temporary tablespace can be defined at the database creation time or by issuing an "ALTER DATABASE"

SQL> alter database default temporary tablespace temp1;

Database altered.


Restrictions :-
1. Default Temporary tablespace cannot be dropped till you create another one.
2. Default Temporary tablespace cannot be taken off-line.

If you define a default temporary tablespace , it's automatically assigned to users.

How to find default temporary tablespace of database


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE          DESCRIPTION
------------------------------                           ------------------------------           ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                               Name of default temporary tablespace

How to change default Temporary Tablespace

1. create a new temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

2. Assign new temporary tablespace as the default temporary tablespace.


SQL> alter database default temporary tablespace temp1;

Database altered.

3. Drop the OLD default temporary tablespace


SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

4. Check that default temporary tablespace changed or not


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE         DESCRIPTION
------------------------------                           ------------------------------         ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP1                           Name of default temporary tablespace

What are Temp Files

Details of Temp files in the database are not recorded in the control file, means we can recreate them whenever we restore the database or after deleting them accidently.

We cannot remove datafiles from a tablespace till we won't drop the entire tablespace. But we can remove tempfiles from a database :-

How to remove tempfiles from a database :-


SQL> alter database tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' drop including datafiles;

Database altered.

How to add tempfiles to a database:-

SQL> alter tablespace temp add tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' size 100m;

Getting Usage Details of Temp tablespace in a database :-

##################### RAC Database ###############################

SQL> Select INST_ID,TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from gv$temp_space_header group by INST_ID,TABLESPACE_NAME;

##################### StandAlone Database ###############################

SQL> Select TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from v$temp_space_header group by TABLESPACE_NAME;

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

SQL> Select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE     ALLOCATED_SPACE      FREE_SPACE
------------------------------                   ---------------                     ---------------                     ----------
TEMP                                       104857600                     8388608                          103809024

########################## INSTANCE WISE ##############################


 SQL> select tablespace_name,
 (free_blocks*8)/1024/1024 FreeSpaceGB,
 (used_blocks*8)/1024/1024 UsedSpaceGB,
 (total_blocks*8)/1024/1024 TotalSpaceGB,
 i.instance_name,i.host_name
 from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
 i.inst_id=ss.inst_id;


TABLESPACE_NAME          FREESPACEGB     USEDSPACEGB    TOTALSPACEGB     INSTANCE_NAME    HOST_NAME
------------------------------            -----------                   -----------                  ------------                    ----------------               ------------------------------
TEMP                                 .007                       .000                      .007                           orcl                          AMIT-PC

I hope this article hepled you.

Regards,
Amit Rath