Recover a table from a RMAN backup on Oracle Database 11g

For those of us who work with 12c Databases the task of recovering dropped tables or older versions of them from RMAN backup is an “automated task” executed by RMAN. It is only necessary one command like this:


RECOVER TABLE myuser.mytab UNTIL TIME '<timestamp_of_table_recover'
AUXILIARY DESTINATION '/mountpoint/with/plentyofspace';

Under the hood RMAN:

  • Creates an auxiliary instance
  • Restores all necessary data files for the affected tablespaces (SYS, SYSAUX, UNDO, and the ones with data to restore)
  • Executes a recover until specified time or SCN
  • Opens the auxiliary database
  • Exports the data
  • Imports the data on origin database
  • Removes the auxiliary database

And with some more parameters it is also possible, for example, to rename the recovered tables or stop the process after the export.

All this must be done manually on an 11g database, and in this post I will try to explain with some detail.

Our example database is an 11gR2 Standard Edition RAC, and in this database I have created a user who owns the tables.


SYS @ orcl1 > create user usuario identified by usuario;

User created.

SYS @ orcl1 > grant connect,resource to usuario;

Grant succeeded.

On this schema I create some tables, one in a tablespace using “Oracle Managed Files”, another on a tablespace with user created files and finally another on a tablespace with mixed files (some Oracle Managed Files and some not).

NOTE: The use of Oracle Managed Files (OMF from now on), and user created files is relevant, specially at the moment of renaming the data files for the cloned database.

For a datafile of type OMF the “uniqueness” inside a diskgroup is determined by the diskgroup’s name, the file’s number and the incarnation’s number (the last two numbers visible at the end of the OMF file name). For example all these names are pointing to the same file:

+DATA/prod/datafile/datafile01.258.850633487
+DATA/clone/datafile/datafile01.258.850633487
+DATA/datafile01.258.850633487
+DATA/prod/datafile/datafile02.258.850633487
+DATA/clone/datafile/datafile02.258.850633487
+DATA/datafile02.258.850633487

The file is uniquely identified by +DATA, 258 and 850633487

In case of OMF files the precedence to create a file name in a duplicate database command is:

  • SET NEWNAME
  • DB_FILE_NAME_CONVERT
  • If a file with the same name (Diskgroup, file_number and incarnation_number) exists the file will get overwritten
  • DB_CREATE_FILE_DEST

To avoid issues (and overwrite any file from origin database) is recommended to use SET NEWNAME and use a different diskgroup for the auxiliary database.

After creating the tablesapaces I create some tables on them

SYS @ orcl1 > create table usuario.omf (nombre varchar2(10)) tablespace omf_tbs;

Table created.

SYS @ orcl1 > create table usuario.manual (nombre varchar2(10)) tablespace manual_tbs;

Table created.

SYS @ orcl1 > create table usuario.mix (nombre varchar2(10)) tablespace mix_tbs;

Table created.

 

And for one of the tables (the one on the mix_tbs tablespace) I have created an index called mix_i outside the tablespace. From now on, the tablespace mix_tbs is no longer “self contained” (it have references to objectes from other tablespaces).


SYS @ orcl1 > CREATE INDEX usuario.mix_i on usuario.mix (nombre) tablespace users;

Index created.

SYS @ orcl1 > insert into usuario.mix values ('uno');

1 row created.

SYS @ orcl1 > insert into usuario.mix values ('dos');

1 row created.

SYS @ orcl1 > insert into usuario.mix values ('tres');

1 row created.

SYS @ orcl1 > commit;

This implies that to recover the table “mix” is necessary to recover the MIX_TBS and also the USERS tablespace, to obtain a “self contained” group of tablespaces (or maybe not.. we view this in detail later).

 

At this point I launch an RMAN Backup to use for the recovering example.


oracle@suse11-112-rac1:~> rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 1 21:43:29 2017

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

connected to target database: ORCL (DBID=1458650138)

RMAN> backup database format '/backup/rman/bckdb_%u_%s_%p' include current controlfile plus archivelog format '/backup/rman/bckarch_%u_%s_%p' delete input;

Starting backup at 01-JAN-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=58 STAMP=931132354
input archived log thread=2 sequence=6 RECID=57 STAMP=931132353
input archived log thread=2 sequence=7 RECID=59 STAMP=931132354

...

tarting backup at 01-JAN-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=38 RECID=109 STAMP=932161470
input archived log thread=2 sequence=29 RECID=110 STAMP=932161471
channel ORA_DISK_1: starting piece 1 at 01-JAN-17
channel ORA_DISK_1: finished piece 1 at 01-JAN-17
piece handle=/backup/rman/bckarch_0srov9u0_28_1 tag=TAG20170101T214432 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+DATA/orcl/archivelog/2017_01_01/thread_1_seq_38.325.932161471 RECID=109 STAMP=932161470
archived log file name=+DATA/orcl/archivelog/2017_01_01/thread_2_seq_29.326.932161471 RECID=110 STAMP=932161471
Finished backup at 01-JAN-17

RMAN> exit

Recovery Manager complete.

oracle@suse11-112-rac1:~> rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 1 21:45:05 2017

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

connected to target database: ORCL (DBID=1458650138)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
-— — — - --- --- -— -— -- —-
23 B A A DISK 01-JAN-17 1 1 NO TAG20170101T214344
24 B F A DISK 01-JAN-17 1 1 NO TAG20170101T214352
25 B F A DISK 01-JAN-17 1 1 NO TAG20170101T214352
26 B A A DISK 01-JAN-17 1 1 NO TAG20170101T214432

Next steps are:

  • To create an auxiliary instance
  • To execute a Tablespace Point in Time recovery (TPR from now on) for a subset of tablespaces
  • To export the data to recover

During this process, if RMAN has connection to origin database, it checks for “auto contention” of recovered tablespaces, in case of detecting some problems it aborts the restoration.

In this case to allow recovering the table in a non “self contained” tablespace we can execute the TPR without connection to origin database. This method (TPR without connection to target database or catalog) implies:

  • We must use SPFILE to start the auxiliary instance
  • We must use systimestamp to specify the recovery time (not SCN)
  • We must specify the name of the undo tablespaces on the recover command
  • We must specify the location of the backup files (they can only be a disk backup)

I have created the needed directories and a password file for the auxiliary (duplicate) database

orapwd file=/app/oracle/product/11.2.0/dbhome_1/dbs/dup password=XXXXX

I have also created an spfile (I created first an pfile and then a spfile from them)


oracle@suse11-112-rac1:/app/oracle/product/11.2.0/dbhome_1/dbs> strings spfiledup.ora
*.compatible='11.2.0.4'
*.db_create_file_dest='+RECUPERA'
*.db_create_online_log_dest_1='+RECUPERA'
*.DB_FILE_NAME_CONVERT='+DATA','+RECUPERA'
*.db_name='DUP'
*.LOG_FILE_NAME_CONVERT='+DATA','+RECUPERA'

 

After that, it is possible to start the “DUP” database in nomount state with the spfile:

oracle@suse11-112-rac1:/backup/rman> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 1 23:27:02 2017

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

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SYS @ dup > shutdown abort
ORACLE instance shut down.
SYS @ dup > startup nomount
ORACLE instance started.

Total System Global Area 254738432 bytes
Fixed Size 2252176 bytes
Variable Size 197132912 bytes
Database Buffers 50331648 bytes
Redo Buffers 5021696 bytes
SYS @ dup > exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters option

 

The command to execute the TPR is this:

RUN {
SET NEWNAME FOR DATABASE TO ‘+RECUPERA’;
set until time “to_date(‘2017-01-01 21:44:30′,’YYYY-MM-DD HH24:MI:SS’)”;
DUPLICATE DATABASE TO DUP
UNDO TABLESPACE ‘UNDOTBS1′,’UNDOTBS2’
TABLESPACE ‘OMF_TBS’,’MIX_TBS’
backup location ‘/backup/rman/’;
}

The SETNEWNAME FOR DATABASE  executes the “SET NEWNAME” for all datafiles changing the diskgroup (avoiding overwriting any original file). The recovery is done on the same server where origin database is located but the files are restored on a separate diskgroup (named RECUPERA).

The set until time specifies the time where the database must be recovered.

Also I specify the name for the duplicate database “DUP”, the name of the UNDO tablespaces on the origin database and the name of the tablespaces to recover (it’s not necessary to specify SYSTEM, SYSAUX  and UNDO ones because are selected by default).

Finally I specify the location of the backup files.

And launch the command:


oracle@suse11-112-rac1:/backup/rman> rman auxiliary /

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jan 1 23:29:30 2017

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

connected to auxiliary database: ORCL (not mounted)

RMAN> RUN {
SET NEWNAME FOR DATABASE TO '+RECUPERA';
set until time "to_date('2017-01-01 21:44:30','YYYY-MM-DD HH24:MI:SS')";
DUPLICATE DATABASE TO DUP
UNDO TABLESPACE 'UNDOTBS1','UNDOTBS2'
TABLESPACE 'OMF_TBS','MIX_TBS'
backup location '/backup/rman/';
}
2> 3> 4> 5> 6> 7> 8>
executing command: SET NEWNAME

executing command: SET until clause

Starting Duplicate Db at 01-JAN-17

contents of Memory Script:
{
sql clone "alter system set control_files =
''+RECUPERA/dup/controlfile/current.266.932167771'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/backup/rman/bckdb_0rrov9tr_27_1';
alter clone database mount;
}
executing Memory Script

sql statement: alter system set control_files = ''+RECUPERA/dup/controlfile/current.266.932167771'' comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''DUP'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes
Variable Size 197132912 bytes
Database Buffers 50331648 bytes
Redo Buffers 5021696 bytes

Starting restore at 01-JAN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=176 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+RECUPERA/dup/controlfile/current.266.932167771
Finished restore at 01-JAN-17

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=176 device type=DISK
Automatically adding tablespace SYSTEM
Automatically adding tablespace SYSAUX
Automatically adding tablespace UNDOTBS1
Automatically adding tablespace UNDOTBS2
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Skipping tablespace USERS
Skipping tablespace READ_ONLY
Skipping tablespace MANUAL_TBS
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects

contents of Memory Script:
{
set until scn 1520355;
set newname for datafile 1 to
"+RECUPERA";
set newname for datafile 2 to
"+RECUPERA";
set newname for datafile 3 to
"+RECUPERA";
set newname for datafile 5 to
"+RECUPERA";
set newname for datafile 6 to
"+RECUPERA";
set newname for datafile 7 to
"+RECUPERA";
set newname for datafile 8 to
"+RECUPERA";
set newname for datafile 9 to
"+RECUPERA";
restore
clone database
skip forever tablespace "USERS",
"READ_ONLY",
"MANUAL_TBS" ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-JAN-17
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 +RECUPERA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +RECUPERA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +RECUPERA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +RECUPERA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +RECUPERA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +RECUPERA
channel ORA_AUX_DISK_1: restoring datafile 00008 to +RECUPERA
channel ORA_AUX_DISK_1: restoring datafile 00009 to +RECUPERA
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman/bckdb_0qrov9so_26_1
channel ORA_AUX_DISK_1: piece handle=/backup/rman/bckdb_0qrov9so_26_1 tag=TAG20170101T214352
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 01-JAN-17

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=932167805 file name=+RECUPERA/dup/datafile/system.265.932167791
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=932167805 file name=+RECUPERA/dup/datafile/sysaux.264.932167791
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=932167805 file name=+RECUPERA/dup/datafile/undotbs1.263.932167791
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=932167805 file name=+RECUPERA/dup/datafile/undotbs2.262.932167791
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=932167805 file name=+RECUPERA/dup/datafile/omf_tbs.261.932167791
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=932167805 file name=+RECUPERA/dup/datafile/omf_tbs.260.932167791
datafile 8 switched to datafile copy
input datafile copy RECID=15 STAMP=932167805 file name=+RECUPERA/dup/datafile/mix_tbs.259.932167791
datafile 9 switched to datafile copy
input datafile copy RECID=16 STAMP=932167805 file name=+RECUPERA/dup/datafile/mix_tbs.258.932167791

contents of Memory Script:
{
set until time "to_date('JAN 01 2017 21:44:30', 'MON DD YYYY HH24:MI:SS')";
recover
clone database
skip forever tablespace "USERS",
"READ_ONLY",
"MANUAL_TBS" delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 01-JAN-17
using channel ORA_AUX_DISK_1

Executing: alter database datafile 4 offline drop
Executing: alter database datafile 12 offline drop
Executing: alter database datafile 10 offline drop
Executing: alter database datafile 11 offline drop
starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=29
channel ORA_AUX_DISK_1: reading from backup piece /backup/rman/bckarch_0srov9u0_28_1
channel ORA_AUX_DISK_1: piece handle=/backup/rman/bckarch_0srov9u0_28_1 tag=TAG20170101T214432
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_38_931121845.dbf thread=1 sequence=38
archived log file name=/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_29_931121845.dbf thread=2 sequence=29
channel clone_default: deleting archived log(s)
archived log file name=/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_38_931121845.dbf RECID=1 STAMP=932167807
channel clone_default: deleting archived log(s)
archived log file name=/app/oracle/product/11.2.0/dbhome_1/dbs/arch2_29_931121845.dbf RECID=2 STAMP=932167807
media recovery complete, elapsed time: 00:00:00
Finished recover at 01-JAN-17
Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes
Variable Size 197132912 bytes
Database Buffers 50331648 bytes
Redo Buffers 5021696 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''DUP'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes
Variable Size 197132912 bytes
Database Buffers 50331648 bytes
Redo Buffers 5021696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+redo', '+recupera' ) SIZE 50 M REUSE,
GROUP 2 ( '+redo', '+recupera' ) SIZE 50 M REUSE
DATAFILE
'+RECUPERA/dup/datafile/system.265.932167791'
CHARACTER SET WE8MSWIN1252

sql statement: ALTER DATABASE ADD LOGFILE

INSTANCE 'i2'
GROUP 3 ( '+recupera', '+redo' ) SIZE 50 M REUSE,
GROUP 4 ( '+recupera', '+redo' ) SIZE 50 M REUSE

contents of Memory Script:
{
set newname for tempfile 1 to
"+RECUPERA";
switch clone tempfile all;
catalog clone datafilecopy "+RECUPERA/dup/datafile/sysaux.264.932167791",
"+RECUPERA/dup/datafile/undotbs1.263.932167791",
"+RECUPERA/dup/datafile/undotbs2.262.932167791",
"+RECUPERA/dup/datafile/omf_tbs.261.932167791",
"+RECUPERA/dup/datafile/omf_tbs.260.932167791",
"+RECUPERA/dup/datafile/mix_tbs.259.932167791",
"+RECUPERA/dup/datafile/mix_tbs.258.932167791";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +RECUPERA in control file

cataloged datafile copy
datafile copy file name=+RECUPERA/dup/datafile/sysaux.264.932167791 RECID=1 STAMP=932167827
cataloged datafile copy
datafile copy file name=+RECUPERA/dup/datafile/undotbs1.263.932167791 RECID=2 STAMP=932167828
cataloged datafile copy
datafile copy file name=+RECUPERA/dup/datafile/undotbs2.262.932167791 RECID=3 STAMP=932167828
cataloged datafile copy
datafile copy file name=+RECUPERA/dup/datafile/omf_tbs.261.932167791 RECID=4 STAMP=932167828
cataloged datafile copy
datafile copy file name=+RECUPERA/dup/datafile/omf_tbs.260.932167791 RECID=5 STAMP=932167828
cataloged datafile copy
datafile copy file name=+RECUPERA/dup/datafile/mix_tbs.259.932167791 RECID=6 STAMP=932167828
cataloged datafile copy
datafile copy file name=+RECUPERA/dup/datafile/mix_tbs.258.932167791 RECID=7 STAMP=932167828

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=932167827 file name=+RECUPERA/dup/datafile/sysaux.264.932167791
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=932167828 file name=+RECUPERA/dup/datafile/undotbs1.263.932167791
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=932167828 file name=+RECUPERA/dup/datafile/undotbs2.262.932167791
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=932167828 file name=+RECUPERA/dup/datafile/omf_tbs.261.932167791
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=932167828 file name=+RECUPERA/dup/datafile/omf_tbs.260.932167791
datafile 8 switched to datafile copy
input datafile copy RECID=6 STAMP=932167828 file name=+RECUPERA/dup/datafile/mix_tbs.259.932167791
datafile 9 switched to datafile copy
input datafile copy RECID=7 STAMP=932167828 file name=+RECUPERA/dup/datafile/mix_tbs.258.932167791

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Dropping offline and skipped tablespaces
Executing: drop tablespace "USERS" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/01/2017 23:30:37
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-12919: Can not drop the default permanent tablespace

RMAN> EXIT

Recovery Manager complete.

 

Despite the last failure (trying to drop one of the tablespaces not included on the recover) the database is open and the table accesible.

All datafiles OMF and not OMF are renamed and relocated to the new Diskgoup.

Note that the oiriginal database is RAC but the DUP is single instance (it is not necessary to start a second instance to export the table data).

 

Take a look at the status of the DUP database, the table and the datafiles:


oracle@suse11-112-rac1:/backup/rman> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 1 23:31:38 2017

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

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SYS @ dup > select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
--- ---- ------------— ---— --- --— - --— -— ---
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
-- - ---— ---- -— -
1 dup suse11-112-rac1 11.2.0.4.0 01-JAN-17 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SYS @ dup > select * from usuario.mix;

NOMBRE

uno
dos
tres

SYS @ dup > select substr(tablespace_name,1,10),substr(file_name,1,50) from dba_data_files;

SUBSTR(TAB SUBSTR(FILE_NAME,1,50)
---------- --------------------------------------------------
USERS	   /app/oracle/product/11.2.0/dbhome_1/dbs/MISSING000
MANUAL_TBS /app/oracle/product/11.2.0/dbhome_1/dbs/MISSING000
SYSTEM	   +RECUPERA/dup/datafile/system.265.932167791
SYSAUX	   +RECUPERA/dup/datafile/sysaux.264.932167791
UNDOTBS1   +RECUPERA/dup/datafile/undotbs1.263.932167791
UNDOTBS2   +RECUPERA/dup/datafile/undotbs2.262.932167791
OMF_TBS    +RECUPERA/dup/datafile/omf_tbs.261.932167791
OMF_TBS    +RECUPERA/dup/datafile/omf_tbs.260.932167791
MIX_TBS    +RECUPERA/dup/datafile/mix_tbs.259.932167791
MIX_TBS    +RECUPERA/dup/datafile/mix_tbs.258.932167791
MANUAL_TBS /app/oracle/product/11.2.0/dbhome_1/dbs/MISSING000
READ_ONLY  /app/oracle/product/11.2.0/dbhome_1/dbs/MISSING000

12 rows selected.

 

And now I try to export the table, both exp and expdp works (in case of expdp it is only necessary to change the user default tablespace to allow expdp create its working tables).


oracle@suse11-112-rac1:/backup/rman> exp userid=usuario/usuario file=/tmp/usuario.dmp log=/tmp/usuario.log

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 23:32:52 2017

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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user USUARIO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user USUARIO
About to export USUARIO's objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export USUARIO's tables via Conventional Path …
. . exporting table MANUAL
EXP-00056: ORACLE error 376 encountered
ORA-00376: file 10 cannot be read at this time
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00010'
. . exporting table MIX 3 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table OMF 0 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.

oracle@suse11-112-rac1:/backup/rman> expdp usuario/usuario directory=temporal dumpfile=usuario_dp.dmp logfile=usuario_dp.log schemas=usuario

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 23:34:36 2017

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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
ORA-31626: job does not exist
ORA-31633: unable to create master table "USUARIO.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS

oracle@suse11-112-rac1:/backup/rman> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 1 23:34:44 2017

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

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SYS @ dup > alter user usuario default tablespace mix_tbs;

User altered.

SYS @ dup > exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
oracle@suse11-112-rac1:/backup/rman> expdp usuario/usuario directory=temporal dumpfile=usuario_dp.dmp logfile=usuario_dp.log schemas=usuario

Export: Release 11.2.0.4.0 - Production on Sun Jan 1 23:35:07 2017

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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
Starting "USUARIO"."SYS_EXPORT_SCHEMA_01": usuario/ directory=temporal dumpfile=usuario_dp.dmp logfile=usuario_dp.log schemas=usuario
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "USUARIO"."MANUAL" failed to load/unload and is being skipped due to error:
ORA-00376: file 10 cannot be read at this time
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10: '/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00010'
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled
. . exported "USUARIO"."MIX" 5.039 KB 3 rows
. . exported "USUARIO"."MANUAL" 0 KB 0 rows
. . exported "USUARIO"."OMF" 0 KB 0 rows
Master table "USUARIO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
**
Dump file set for USUARIO.SYS_EXPORT_SCHEMA_01 is:
/tmp/usuario_dp.dmp
Job "USUARIO"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at Sun Jan 1 23:35:43 2017 elapsed 0 00:00:31

 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s