RESTORE TABLE FROM RMAN BACKUP ORACLE12c database

The RECOVER TABLE command is a new feature in 12C that allows point in time recovery of a table or a table partition.
The table is recovered into an auxiliary instance and there is the option to:

• import the recovered table into a new table or partition using REMAP option
• create the expdp dump of the recovered table only, for import at a later time of your choosing

Pre-requisites:

• The target database must be in read-write mode.
• The target database must be in ARCHIVELOG mode.
• You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover  these objects.
• To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher.

Setting the Point-in-time to Which Tables and Table Partitions Must be Recovered

Options are:

• SCN
• Time
• Sequence number

The ROOT container SYSTEM and UNDO tablespaces are restored to the auxiliary therefore RECOVER TABLE has to be run after a direct connection to ROOT CDB.  If a service_name is not used, RMAN will fail during export:

SELECT SEQUENCE#, THREAD#, FIRST_TIME, NEXT_TIME   FROM V$ARCHIVED_LOG

run
{
RECOVER TABLE "SCOTT"."EMP"
UNTIL SEQUENCE 10 thread 1
AUXILIARY DESTINATION '/u01/app/oracle/oradata/HLRMAIN3'
REMAP TABLE 'SCOTT'.'EMP':'EMP_PREV';
}

OR

run
{
RECOVER TABLE "SCOTT"."SALGRADE"
UNTIL time "TO_DATE('09-JUN-14 14:00:00','DD-MON-YY HH24:MI:SS')"
AUXILIARY DESTINATION '/u01/app/oracle/oradata/HLRMAIN3'
REMAP TABLE 'SCOTT'.'SALGRADE':'SALGRADE_PREV';
}

the out put is


RMAN> run
2> {
RECOVER TABLE "SCOTT"."EMP"
UNTIL SEQUENCE 10 thread 1
AUXILIARY DESTINATION '/u01/app/oracle/oradata/HLRMAIN3'
REMAP TABLE 'SCOTT'.'EMP':'EMP_PREV';
}3> 4> 5> 6> 7>

Starting recover at 09-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=346 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=207 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=253 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=347 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='zmww'

initialization parameters used for automatic instance:
db_name=HLRMAIN2
db_unique_name=zmww_pitr_HLRMAIN2
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/app/oracle/oradata/HLRMAIN3
log_archive_dest_1='location=/u01/app/oracle/oradata/HLRMAIN3'
#No auxiliary parameter file used


starting up automatic instance HLRMAIN2

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                293602560 bytes
Database Buffers             767557632 bytes
Redo Buffers                   5480448 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  logseq 10 thread 1;
# 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';
}
executing Memory Script

executing command: SET until clause

Starting restore at 09-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=74 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=64 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=83 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=92 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 /u01/RMAN/c-1520971765-20140609-02
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN/c-1520971765-20140609-02 tag=TAG20140609T141102
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl
Finished restore at 09-JUN-14

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  logseq 10 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone tempfile  1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 4, 3;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-JUN-14
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

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 00004 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN/HLRMAIN2_S_67_P_1_T_849795008
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u01/RMAN/HLRMAIN2_S_66_P_1_T_849795008
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_3: reading from backup piece /u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN/HLRMAIN2_S_67_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_2: piece handle=/u01/RMAN/HLRMAIN2_S_66_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_3: piece handle=/u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:55
Finished restore at 09-JUN-14

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=849797136 file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_system_9sc7k9pc_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=849797136 file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_undotbs1_9sc7k9ox_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=849797136 file name=/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_sysaux_9sc7k9pk_.dbf

contents of Memory Script:
{
# set requested point in time
set until  logseq 10 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

Starting recover at 09-JUN-14
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-JUN-14

sql statement: alter database open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                297796864 bytes
Database Buffers             763363328 bytes
Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes
Variable Size                297796864 bytes
Database Buffers             763363328 bytes
Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  logseq 10 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  6;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 09-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=64 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=74 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=83 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=92 device type=DISK

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 00006 to /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN/HLRMAIN2_S_65_P_1_T_849795008 tag=TAG20140609T141008
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-JUN-14

datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=849797166 file name=/u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/datafile/o1_mf_users_9sc7mx3h_.dbf

contents of Memory Script:
{
# set requested point in time
set until  logseq 10 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile  6 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

Starting recover at 09-JUN-14
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_7_9sc64ppq_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_8_9sc68zpq_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/fast_recovery_area/HLRMAIN2/archivelog/2014_06_09/o1_mf_1_9_9sc6tod8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-JUN-14

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/HLRMAIN3''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/HLRMAIN3''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/HLRMAIN3''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/HLRMAIN3''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_zmww_uaxg":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
    EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_ADMINMSG_BUS_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_EM_EVENT_BUS_TABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_NOTIFY_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_EM_NOTIFY_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_HOST_PING_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_EM_CNTR_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_MGMT_LOADER_QTABLE_S','SYSMAN',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/app/oracle/oradata/HLRMAIN2/mgmt.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
   EXPDP> . . exported "SCOTT"."EMP"                               8.757 KB      14 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_zmww_uaxg" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_zmww_uaxg is:
   EXPDP>   /u01/app/oracle/oradata/HLRMAIN3/tspitr_zmww_74786.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_zmww_uaxg" completed with 7 error(s) at Mon Jun 9 14:47:07 2014 elapsed 0 00:00:45
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_zmww_eCcE" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_zmww_eCcE":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "SCOTT"."EMP_PREV"                          8.757 KB      14 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_zmww_eCcE" successfully completed at Mon Jun 9 14:47:30 2014 elapsed 0 00:00:15
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_temp_9sc7m2pz_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/onlinelog/o1_mf_3_9sc7mzvf_.log deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/onlinelog/o1_mf_2_9sc7mznf_.log deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/onlinelog/o1_mf_1_9sc7mzfb_.log deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/ZMWW_PITR_HLRMAIN2/datafile/o1_mf_users_9sc7mx3h_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_sysaux_9sc7k9pk_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_undotbs1_9sc7k9ox_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/datafile/o1_mf_system_9sc7k9pc_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/HLRMAIN3/HLRMAIN2/controlfile/o1_mf_9sc7k0hg_.ctl deleted
auxiliary instance file tspitr_zmww_74786.dmp deleted
Finished recover at 09-JUN-14

http://www.pythian.com/blog/table-recovery-with-rman-in-database-12c/

ORA-24248 XMLDB extensible security not installed

Oracle 11g introduces more secure and fine grained access on network packages like UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR, i.e. an execute privilege on these packages is not enough to access an external network resource using these packages. You have to configure ACL (Access Control List), assign the network host and port to it and grant connect privilege to the users through this ACL .

Please see: Oracle 11g Access Control List for External Network Services

These ACLs are XML files which are stored in the XML Database repository inside the database itself, which means that XML Database must be installed in the database to use the network PL/SQL packages. XML Database is installed automatically when you create a database using DBCA with default options. However, if you unselect/uncheck the XML Database option in DBCA while creating the database, XML Database won't installed.

A few days ago I ran into this problem. We upgraded one of our development database from oracle 10gR2 to 11gR1 recently. After the upgrade the application was broken very badly. Any user trying to login to the application was getting the error below:

ORA-24248: XMLDB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

After I investigated, I found that that in one of Java classes in the application, where a connection was being established to the databases, a query was being executed like this.

SELECT global_name,utl_inaddr.get_host_address FROM global_name;

When I saw UTL_INADDR being called, I realized instantly that we need an ACL for this call and ACL needs XML Database. The error above is quite self explanatory. I went ahead and verified the existence of Oracle XML Database and found the following:

SQL> select comp_name from dba_registry;

COMP_NAME
----------------------------------------
OWB
Oracle Application Express
Oracle Enterprise Manager
OLAP Catalog
Spatial
Oracle Multimedia
Oracle Text
Oracle Expression Filter
Oracle Rules Manager
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API

17 rows selected.

As can be seen from the output of the above query, we were missing Oracle XML Database. I was able to reproduce the problem in sqlplus.

SQL> conn scott/tiger
Connected.

SQL> select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

SQL>

Install XML Database: Oracle 11g Enterprise Edition

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

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

SQL> spool install_xml_db.log
SQL> @catqm xdb sysaux temp YES
-- xdb is the password for XML Database repository schema
-- sysaux is the default tablespace for XML Database repository schema.
-- temp is the default temporary tablespace for XML Database repository schema.
-- YES is for using secure files for xdb$resource
.
.
.
[output trimmed]
.
.
.
SQL> declare
  2    suf  varchar2(26);
  3    stmt varchar2(2000);
  4  begin
  5    select toksuf into suf from xdb.xdb$ttset where flags = 0;
  6    stmt := 'grant all on XDB.X$PT' || suf || ' to DBA';
  7    execute immediate stmt;
  8    stmt := 'grant all on XDB.X$PT' || suf || ' to SYSTEM WITH GRANT OPTION';
  9    execute immediate stmt;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>spool off

The XML Database installation is complete. I opened the install_xml_db.log and didn't find any significant errors in it. Then I verified from dba_registry again to see the status of XML Database installed.

SQL> select comp_name , status from dba_registry;

COMP_NAME                                STATUS
---------------------------------------- --------------------------------------------
Oracle XML Database                      VALID
OWB                                      VALID
Oracle Application Express               VALID
Oracle Enterprise Manager                VALID
OLAP Catalog                             VALID
Spatial                                  VALID
Oracle Multimedia                        VALID
Oracle Text                              VALID
Oracle Expression Filter                 VALID
Oracle Rules Manager                     VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             VALID
Oracle XDK                               VALID
Oracle Database Java Packages            VALID
OLAP Analytic Workspace                  VALID
Oracle OLAP API                          VALID

18 rows selected.

SQL>

The status should be VALID to indicate that the installation was successfull. Then I tested the UTL_INADDR again.

SQL> conn scott/tiger
Connected.
SQL> select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

As you see now the ORA-24248 is gone because we have XML Database installed. Now we are seeing ORA-24247, which means we can't access any network host using UTL_INADDR unless we allow access to this host via ACL.

SQL> conn / as sysdba
Connected.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'my_application_acl.xml',
        description  => 'ACL for users of my application.',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'resolve',
        start_date   => null,
        end_date     => null
 );
END;
/

PL/SQL procedure successfully completed.

/*
 All that UTL_INADDR.get_host_address does is resolve the host and return the IP Address.
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL creates an ACL and grant privilege to a user or role 
 to that ACL.
 For the sake of this article I am using SCOTT as the grantee of the privilege resolve but 
 in my application I granted this privilege to a role which was assigned to all the 
 application users. So through that role all the users were granted resole privilege to 
 this ACL.
*/

BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
     acl         =>'my_application_acl.xml',
     host        => 'localhost',
     lower_port  => null,
     upper_port  => null
     );
END;
/

PL/SQL procedure successfully completed.

/*
 When UTL_INADDR.get_host_address is invoked without a host name specified with it,
 it sends the request to localhost. So we need to assign our ACL to the host "localhost".
*/

commit;

SQL> conn scott/tiger
Connected.
SQL> set lines 10000
SQL> column global_name format a20
SQL> column get_host_address format a15
SQL> select global_name,utl_inaddr.get_host_address from global_name;

GLOBAL_NAME          GET_HOST_ADDRES
-------------------- ---------------
ORA11G.HOME.COM      127.0.0.1

SQL>

As you can see above, SCOTT is now able to get server's IP Adress using utl_inaddr.get_host_address. And this solves our problem with the application.

Uninstall XML Database: Oracle 11g Enterprise Edition

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

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

SQL> @catnoqm.sql
-- This will uninstall the Oracle XML Database.

......Zahid 

DB_ULTRA_SAFE a new GEM for High Availability

DB_ULTRA_SAFE is a new parameter introduced with Oracle 11gR1, and a fantastic new GEM for High Availability, that using Data Guard to configure on both the primary and standby will trigger the most comprehensive data corruption prevention and detection (and repair on 11gR2, see **) tool in the market.

** Starting in Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

Speaking simple, what this new functionality will do is use your Standby Database as a backup to correct automatically any data corruption on your primary database and vice-versa (again on 11.2).

The DB_ULTRA_SAFE initialization parameter also controls other data protection behavior in Oracle Database, such as requiring ASM to perform sequential mirror write I/Os.

You basically need to understand that when setting DB_ULTRA_SAFE it will control the behaviour of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters for you, which mean:

When you set DB_ULTRA_SAFE to	Then the following parameters…
DATA_AND_INDEX (recommended by Oracle)	DB_BLOCK_CHECKING is set to FULL. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.
DATA_ONLY	DB_BLOCK_CHECKING is set to MEDIUM. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.

Lets’ Check all the parameters affected by DB_ULTRA_SAFE:

·                   DB_BLOCK_CHECKING (Introduced with Oracle 8.1.6) prevents memory and data corruptions, but it incurs some performance overhead on every block change.

·                   DB_BLOCK_CHECKSUM (Introduced with Oracle 8.1.6) detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.

·                   DB_LOST_WRITE_PROTECT (also introduced with 11gR1) enable or disable a physical standby database to detect lost write corruptions on both the primary and physical standby database.

Important: if you explicitly set the DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters in the initialization parameter file, then the DB_ULTRA_SAFE parameter has no effect and no changes are made to the parameter values. Thus, if you specify the DB_ULTRA_SAFE parameter, do not explicitly set these underlying parameters.

To activate it, all you need to do is follow the following steps:

On the Primary Database:

1.      Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:

2.      SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;

3.      SQL> shutdown immediate (Shutdown your Database)

4.      SQL> startup (This will start your primary Database using your new parameter set in the SPFILE previously)

On the Physical Standby Database:

1.      Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:

2.      SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;

3.      SQL> startup nomount

4.      SQL> alter database mount standby database;

5.      SQL> alter database recover managed standby database disconnect from session;

If you are using your Standby Database on Read Only mode you also need to run the follow commands on your Physical Standby DB:

1.      SQL> alter database recover managed standby database cancel;

2.      SQL> alter database open read only;

If you decide to change later the Read Only Standby to Standby again, you just will need to run the following command:

1.      SQL> alter database recover managed standby database disconnect from session;

Hoping this information could help you in the future,

by   Francisco Munoz Alvarez

Connect Time Failover & Transparent Application Failover for Data Guard

Connect Time Failover & Transparent Application Failover for Data Guard

 

I was giving a 10g Data Guard course this week in Düsseldorf, demonstrating amongst others the possibility to configure Transparent Application Failover (TAF) for Data Guard. I always try to keep things as simple as seriously possible, in order to achieve an easy and good understanding of what I like to explain. Later on, things are getting complex by themselves soon enough

In my simple scenario, I have one Primary Database (prima) and one Physical Standby Database (physt). On my downloads page, you may find an installation guide for that scenario for 10g and for 11g. After a switchover or after a failover, the primary is going to be physt. The challenge is now to get the connect from the client side to the right (primary) database. That is called Connect Time Failover and is achieved as follows:

First, we make sure that the client uses a tnsnames.ora with a connect descriptor that uses a SERVICE_NAME instead of a SID

MYAPP =

 (DESCRIPTION =

 (ADDRESS_LIST =

 (ADDRESS = (PROTOCOL = TCP)(HOST = HostA)(PORT = 1521))

 (ADDRESS = (PROTOCOL = TCP)(HOST = HostB)(PORT = 1521))

 )

 (CONNECT_DATA =

 (SERVICE_NAME = myapp)

 )

 )

HostA is the host on which prima runs, HostB has physt running.

Second, we take care that the service myapp is offered only at the right database – only on the primary.Notice that the PMON background processes of both databases must be able to communicate with the (local) listeners in order to register the service myapp. If you don’t use the listener port 1521, they can’t. You have to point to that listener port then with the initialization parameter LOCAL_LISTENER.

We create and start now the service myapp manually on the primary:

begin

 dbms_service.create_service('myapp','myapp');

end;

/

begin

 DBMS_SERVICE.START_SERVICE('myapp');

end;

/

Then we create a trigger, that ensures that this service is only offered, if the database is in the primary role:

create trigger myapptrigg after startup on database

declare

 v_role varchar(30);

begin

 select database_role into v_role from v$database;

 if v_role = 'PRIMARY' then

 DBMS_SERVICE.START_SERVICE('myapp');

 else

 DBMS_SERVICE.STOP_SERVICE('myapp');

 end if;

end;

/

The event after startup is fired, if an instance changes from status MOUNT to OPEN. If you use a logical standby, it is not fired, because the logical standby remains in status OPEN. You may use the event after db_role_change in this case. The creation of the trigger and of the service is accompanied with redo protocol (the Data Dictionary has changed) and therefore also present at physt without any additional work to do there for the DBA. With the present setup, we have already achieved Connect Time Failover: Clients can use the same connect descriptor (myapp) to get to the right (primary) database now, regardless of switchover or failover.

But sessions that are connected to prima are disconnected if a switchover or failover to physt takes place. They have got to connect again then. We can change that, so that a Runtime Failover is possible, under ideal circumstances, that failover is even completely transparent to the client and proceeds without error messages. To achieve that, you don’t have to touch the tnsnames.ora on the client side. Instead, you do the following on the primary database:

begin

 dbms_service.modify_service

 ('myapp',

 FAILOVER_METHOD => 'BASIC',

 FAILOVER_TYPE => 'SELECT',

 FAILOVER_RETRIES => 200,

 FAILOVER_DELAY => 1);

end;

/

Connections to the service myapp are now automatically failed over together with the service to the new primary. Should they have done nothing during the time of the failover/switchover, or even if they had run a select statement, they will not receive any error but only notice a short interruption (about 20 seconds, in a typical case). Only if sessions have open transactions during the failover/switchover, they will receive error messages (“transaction must roll back”) after they try commit then.

I use to demonstrate that with a select on a table with 100000 rows that starts on the primary. Then I kill the SMON of that primary and the select stops at row 30000 something, waits a couple of seconds (maximal 200, with the above settings) and then continues on the new primary after the failover, fetching exactly the 100000 rows! That is always quite impressive and shows how robust Oracle Databases – especially combined with Data Guard – are

 

by Uwe Hesse,

How to change database character set

This article gives a overview of methods to change the database character set .


Change Oracle Database Character Set : NLS_CHARACTERSET

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

ALTER DATABASE db_name CHARACTER SET new_character_set;

db_name is optional. The character set name should be specified without quotes. For example:

ALTER DATABASE CHARACTER SET AL32UTF8;

To change the database character set, perform the following steps:

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
Complete the following statements:
4. STARTUP MOUNT;

5. ALTER SYSTEM ENABLE RESTRICTED SESSION;

6. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

7. ALTER SYSTEM SET AQ_TM_PROCESSES=0;

8. ALTER DATABASE OPEN;

9. ALTER DATABASE CHARACTER SET new_character_set;

10. SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

11. STARTUP;

SQL > select * from nls_database_parameters ORDER BY PARAMETER;

Transportable Tablespaces

Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. In Oracle 8i one of the restrictions was that the block size of both databases must be the same. In Oracle 9i the introduction of multiple block sizes has removed this restriction. In this article I will run through a simple example of transporting a tablespace between two databases.


Oracle Data Pump in Oracle Database 10g (expdp and impdp)
Data Pump Enhancements in Oracle Database 11g Release 1
SQL Developer 3.1 Data Pump Wizards (expdp, impdp)


For this example I'm going to create a new tablespace, user and table to work with in the source database.

CONN / AS SYSDBA

CREATE TABLESPACE test_data
  DATAFILE '/u01/app/oracle/oradata/DB11G/test_data01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test_user IDENTIFIED BY test_user
  DEFAULT TABLESPACE test_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON test_data;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

CONN test_user/test_user

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;

COMMIT;
Source Database

For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check.

CONN / AS SYSDBA
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL>
The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.

SELECT * FROM transport_set_violations;

no rows selected

SQL>
Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode.

SQL> ALTER TABLESPACE test_data READ ONLY;

Tablespace altered.

SQL>
Next we export the tablespace metadata using the export (expdp or exp) utility. If you are using 10g or above you should use the expdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now export the tablespace metadata.

$ expdp userid=system/password directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log
Copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy.

The source tablespace can now be switched back to read/write mode.

ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

CONN / AS SYSDBA

CREATE USER test_user IDENTIFIED BY test_user;
GRANT CREATE SESSION, CREATE TABLE TO test_user;
Now we import the metadata into the destination database. If you are using 10g or above you should use the impdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now import the tablespace metadata.

$ impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.

$ imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data file=test_data.dmp log=test_data_imp.log
Switch the new tablespace into read write mode.

SQL> ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>
The tablespace is now available in the destination database.

SELECT tablespace_name, plugged_in, status
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_DATA';

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
TEST_DATA                      YES ONLINE

1 row selected.

SQL>


By Tim.....