Skip to content

Recover Dropped table with RMAN 12c New feature

September 18, 2013

Recover Dropped table with RMAN 12c New feature.
 
If you are testing on personal hardware, make sure you have sufficient Hardware to test the same.
 
Note: Below (system verify) can be ignored if you have high end resources.
 
System Verify:
Have minimum server resources as below on Solaris 10:
(a) RAM/Memory:  1800M (more the better to avoid memory issues while recovering dropped table while RMAN clone is executed internally/automatically)
(b) Kernel Parameter Shared Memory set to minimum: 4G (more the better )
As root, you can set 4G or more for shared memory   :
# projmod -sK “project.max- shm-memory=(privileged,4G,deny)” group.oinstall
 
As Oracle verify Shared Memory:
# prctl -n project.max-shm-memory -i project group.oinstall
project: 100: group.oinstall
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
privileged      4.00GB      –   deny                                 –
system          16.0EB    max   deny                                 –
(c) Database SGA Size keep to 350M as memory target when your system has less Memory.
(d) Swap size :  3.5 GB  ( more the better )
If you need manually add more Swap temporarily , follow below:
As root:
(i) Create swap file of 1G, you can increase size as far as file system or mount point has sufficient disk space.
# df -h |egrep ‘File|swap’
# swap -s
# /usr/sbin/mkfile 1024m /export/home/oracle/tmp/oracle_install_swap
(ii) Activate the swap file to be used by the system:
# /usr/sbin/swap -a /export/home/oracle/tmp/oracle_install_swap
# df -h |egrep ‘File|swap’
# swap –s
(iii) To keep the allocated swap space to persist reboots and to /etc/vfstab file.
# cat /etc/vfstab
#device         device          mount           FS      fsck    mount   mount
#to mount       to fsck         point           type    pass    at boot options
#
….
….
/export/home/oracle/tmp/oracle_install_swap  –       –       swap    –       no      –
(iv) To deallocate swap space
# /usr/sbin/swap -d /export/home/oracle/tmp/oracle_install_swap
# rm /export/home/oracle/tmp/oracle_install_swap
# swap –s
Finally remove entry from /etc/vfstab if added.
 
1) Create table mtest.emp and add few records.
create table mtest.emp (empno number(4), ename varchar2(15));
insert into mtest.emp (1,’MALU’);
insert into mtest.emp (2,’RAM’);
insert into mtest.emp (3,’SAM’);
commit;
Alter system switch logfile;
Alter system switch logfile;
SQL> select name from v$database;
NAME
———
D041
SQL> select * from  mtest.emp;
EMPNO ENAME
———- —————————-
1 MALU
2 RAM
3 SAM
 
2) Run Full Rman backup 
rman target /
RUN
{
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT SEQUENCE# FROM V$LOG WHERE STATUS = ‘CURRENT’;
ALLOCATE CHANNEL c1 DEVICE TYPE DISK MAXPIECESIZE 1G FORMAT ‘/export/home/oracle/backup/DB_d041_%U’;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK MAXPIECESIZE 1G FORMAT ‘/export/home/oracle/backup/DB_d041_%U’;
SET COMMAND ID TO ‘rman_d041_full_lvl_0’;
BACKUP
INCREMENTAL LEVEL 0
skip inaccessible
TAG backup_whole_level_01
filesperset=2
DATABASE ;
ALTER SYSTEM ARCHIVE LOG CURRENT;
release channel c1;
release channel c2;
SELECT SEQUENCE# FROM V$LOG WHERE STATUS = ‘CURRENT’;
#backup all archive logs
allocate channel c3 type disk ;
backup
format ‘/export/home/oracle/backup/archive_d041_%t_%s.arc’
archivelog all;
#(archivelog all delete input);
release channel c3;
#backup controlfile
allocate channel c4 type disk ;
backup
format ‘/export/home/oracle/backup/cntrl_%s_%p_%t’
current controlfile;
release channel c4;
}
list backup of database TAG backup_whole_level_01;
 
3) Add few more records  after backup.
insert into mtest.emp (4,’TOM’);
insert into mtest.emp (5,’PAM’);
commit;
Alter system switch logfile;
Alter system switch logfile;
SQL>  select owner,TABLE_NAME,TABLESPACE_NAME  from dba_tables  where owner=’MTEST’ AND TABLE_NAME=’EMP’;
OWNER      TABLE_NAME                     TABLESPACE_NAME
———- —————————— ——————————
MTEST      EMP                            MDATA
SQL> select * from mtest.emp;
EMPNO ENAME
———- —————————-
1 MALU
2 RAM
3 SAM
4 TOM
5 PAM
SQL> select current_scn from v$database;
CURRENT_SCN
———–
657938
Using SCN number we can find date and time of that sequence. As current_scn gets incremented every second, similar to time.
SQL> SELECT SCN_TO_TIMESTAMP (657938) from dual;
SCN_TO_TIMESTAMP(657938)
————————————————-
16-SEP-13 04.34.47.000000000 PM
 
 
SQL> drop table mtest.emp;                      è Table is Dropped here
Table dropped.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oradata/d041/arch/
Oldest online log sequence     22
Next log sequence to archive   24
Current log sequence           24
 
4) Different option with UNTIL Clause
 
http://docs.oracle.com/cd/E16655_01/backup.121/e17630/rcmresind.htm#BEIICGGJ
Though we have different option with UNTIL Clause as below, we tried until time option.
(a)    SCN
Recovers tables or table partitions to the state that they were at the time specified by the SCN.
UNTIL SCN 657938
(b)  Time
Recovers tables or table partitions to the state they were in at the specified time.
For example:
Till time : 16-sep-13 16hrs 34mins 47 secs
——————————————————–
UNTIL TIME “TO_DATE(’09/16/13 16:34:47′,’MM/DD/YY HH24:MI:SS’)”
Or
You can also use data constants such as SYSDATE to specify the time,
4 days before the current date:
———————————————————
UNTIL TIME ‘SYSDATE-4’
Or
Point in time 30 minutes ago (just before we dropped the table)
————————————————————————————–
UNTIL TIME ‘sysdate-30/1440’
(c) Sequence number
Recovers tables or table partitions to the state they were at the time specified by the log sequence number and thread number.
UNTIL SEQUENCE 354
 
5) About Importing Recovered Tables into the Target Database.
 
By default, RMAN imports the recovered tables or table partitions, which are stored in the export dump file, into the target database. However, you can choose not to import the recovered tables or table partitions by using the NOTABLEIMPORT clause of the RESTORE command.
 
When NOTABLEIMPORT is used, RMAN recovers them to the specified point and then creates the export dump file. However, this dump file is not imported into the target database. You must manually import this dump file into your target database, when required, by using the Data Pump Import utility.
 
If an error occurs during the import operation, RMAN does not delete the export dump file at the end of the table recovery. This enables you to manually import the dump file.
 
Different Ways to Recover the table:
————————————————————————————–
(a)  For recovering with same name as it was dropped.
RECOVER TABLE MTEST.EMP
UNTIL TIME “TO_DATE(’09/16/13 16:34:47′,’MM/DD/YY HH24:MI:SS’)”
AUXILIARY DESTINATION ‘/export/home/oracle/backup/recv’
DATAPUMP DESTINATION ‘/export/home/oracle/backup/recv/dump’;
 
(b)  For recovering with different name E.g.  recover EMP table with name EMP_RECOVERED.
RECOVER TABLE MTEST.EMP
UNTIL SCN 657938
AUXILIARY DESTINATION ‘/export/home/oracle/backup/recv’
DATAPUMP DESTINATION ‘/export/home/oracle/backup/recv/dump’
REMAP TABLE ‘MTEST’.’EMP’:’EMP_RECOVERED’;
 
(c)  NOTABLEIMPORT : RMAN recovers them to the specified point and then creates the export dump file. Later you can manually import into target database.
RECOVER TABLE MTEST.EMP
UNTIL SCN 657938
AUXILIARY DESTINATION ‘/export/home/oracle/backup/recv’
DATAPUMP DESTINATION ‘/export/home/oracle/backup/recv/dump’
DUMP FILE ’emp_exp_dump.dat’
NOTABLEIMPORT;
 
(d)  For recovering multiple tables.
RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
UNTIL SCN 657938
AUXILIARY DESTINATION ‘/export/home/oracle/backup/recv’
DATAPUMP DESTINATION ‘/export/home/oracle/backup/recv/dump’;
 
6) Space required for restore.
Disk Space required depends on the size of your tablespaces that is being restored.
(a)Rman would restore “SYSTEM”, “UNDOTBS1”, “SYSAUX” and the tablespace in which the table exist in this case it would restore MDATA tablespace.
Snip from recover log:
=========================
recover clone database tablespace  “MDATA”, “SYSTEM”, “UNDOTBS1”, “SYSAUX” delete archivelog;
 
(b)Would need space for Archive files to be restored.
 
(c) Would need space for Export dump file that would get created as part of expdp (export) utility.
 
7) Steps Performed By RMAN to Recover Tables RMAN performs the following steps while automating the process of recovering tables or table partitions from an RMAN backup:(1) Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery.(2) Creates an auxiliary database and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database.You can specify the location to which the recovered data files are stored in the auxiliary database.

(3) Creates a Data Pump export dump file that contains the recovered tables or table partitions.

You can specify the name and the location of the export dump file used to store the metadata of the recovered tables or table partitions.

(4) (Optional) Imports the Data Pump export dump file into the target instance.

You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database.

If you do not import the export dump file as part of the recovery process, you must manually import it later using the Data Pump Import utility.

(5) (Optional) renames the recovered tables or table partitions in the target database.

Example: Recovery Based on Time
Note : As below  auxiliary SID=’aorD’ sets  sga_target=1G automatically , if your system has less resources or memory , you can run below two commands to decrease SGA size and release some memory, else your system may start swapping and recovery may fail due to memory/swap issue.
*******But do not change the same in real scenario. *****
 
alter system set sga_target=350M;
alter system set pga_aggregate_limit=1807M;
 
I have highlighted few actions below, to understand better as what exactly it does:
 
Script started on Tue Sep 17 21:40:56 2013
ora12crac1:/export/home/oracle: rman target /
Recovery Manager: Release 12.1.0.1.0 – Production on Tue Sep 17 21:41:02 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: D041 (DBID=375915797)
RMAN> RECOVER TABLE MTEST.EMP
2> UNTIL TIME “TO_DATE(’09/16/13 16:34:47′,’MM/DD/YY HH24:MI:SS’)”
3> AUXILIARY DESTINATION ‘/export/home/oracle/backup/recv’
4> DATAPUMP DESTINATION ‘/export/home/oracle/backup/recv/dump’;
Starting recover at 17-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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=’aorD’
initialization parameters used for automatic instance:
db_name=D041
db_unique_name=aorD_pitr_D041
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=/export/home/oracle/backup/recv
log_archive_dest_1=’location=/export/home/oracle/backup/recv’
#No auxiliary parameter file used
starting up automatic instance D041
Oracle instance started
Total System Global Area    1068937216 bytes
Fixed Size                     2369296 bytes
Variable Size                281020656 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5406720 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until  time “TO_DATE(’09/16/13 16:34:47′,’MM/DD/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’;
}
executing Memory Script
executing command: SET until clause
Starting restore at 17-SEP-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 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/app/oracle/product/12.1.0/dbhome_1/dbs/c-375915797-20130916-02
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/c-375915797-20130916-02 tag=TAG20130916T162707
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/export/home/oracle/backup/recv/D041/controlfile/o1_mf_93jzvxyf_.ctl
Finished restore at 17-SEP-13
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  time “TO_DATE(’09/16/13 16:34:47′,’MM/DD/YY HH24:MI:SS’)”;
# 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;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2;
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 /export/home/oracle/backup/recv/D041/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 17-SEP-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 00002 to /export/home/oracle/backup/recv/D041/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /export/home/oracle/backup/DB_d041_31ok0nnt_1_1
channel ORA_AUX_DISK_1: piece handle=/export/home/oracle/backup/DB_d041_31ok0nnt_1_1 tag=BACKUP_WHOLE_LEVEL_01
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:54
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 /export/home/oracle/backup/recv/D041/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /export/home/oracle/backup/DB_d041_30ok0nnt_1_1
channel ORA_AUX_DISK_1: piece handle=/export/home/oracle/backup/DB_d041_30ok0nnt_1_1 tag=BACKUP_WHOLE_LEVEL_01
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:46
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 00003 to /export/home/oracle/backup/recv/D041/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /export/home/oracle/backup/DB_d041_32ok0nsl_1_1
channel ORA_AUX_DISK_1: piece handle=/export/home/oracle/backup/DB_d041_32ok0nsl_1_1 tag=BACKUP_WHOLE_LEVEL_01
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 17-SEP-13
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=826408173 file name=/export/home/oracle/backup/recv/D041/datafile/o1_mf_system_93k069q1_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=826408175 file name=/export/home/oracle/backup/recv/D041/datafile/o1_mf_undotbs1_93k07t1b_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=826408175 file name=/export/home/oracle/backup/recv/D041/datafile/o1_mf_sysaux_93k04m4b_.dbf
contents of Memory Script:
{
# set requested point in time
set until  time “TO_DATE(’09/16/13 16:34:47′,’MM/DD/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”;
# 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  3 online
sql statement: alter database datafile  2 online
Starting recover at 17-SEP-13
using channel ORA_AUX_DISK_1
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=16
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=19
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=21
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_AUX_DISK_1: reading from backup piece /export/home/oracle/backup/archive_d041_826303083_104.arc
channel ORA_AUX_DISK_1: piece handle=/export/home/oracle/backup/archive_d041_826303083_104.arc tag=TAG20130916T163802
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/export/home/oracle/backup/recv/1_16_826154360.dbf thread=1 sequence=16
archived log file name=/export/home/oracle/backup/recv/1_17_826154360.dbf thread=1 sequence=17
archived log file name=/export/home/oracle/backup/recv/1_18_826154360.dbf thread=1 sequence=18
archived log file name=/export/home/oracle/backup/recv/1_19_826154360.dbf thread=1 sequence=19
archived log file name=/export/home/oracle/backup/recv/1_20_826154360.dbf thread=1 sequence=20
archived log file name=/export/home/oracle/backup/recv/1_21_826154360.dbf thread=1 sequence=21
archived log file name=/export/home/oracle/backup/recv/1_22_826154360.dbf thread=1 sequence=22
media recovery complete, elapsed time: 00:00:21
Finished recover at 17-SEP-13
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 =
”/export/home/oracle/backup/recv/D041/controlfile/o1_mf_93jzvxyf_.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     367439872 bytes
Fixed Size                     2361208 bytes
Variable Size                281020552 bytes
Database Buffers              75497472 bytes
Redo Buffers                   8560640 bytes
sql statement: alter system set  control_files =   ”/export/home/oracle/backup/recv/D041/controlfile/o1_mf_93jzvxyf_.ctl” comment= ”RMAN set”
scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     367439872 bytes
Fixed Size                     2361208 bytes
Variable Size                281020552 bytes
Database Buffers              75497472 bytes
Redo Buffers                   8560640 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until  time “TO_DATE(’09/16/13 16:34:47′,’MM/DD/YY HH24:MI:SS’)”;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  5 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  5;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 17-SEP-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 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 00005 to /export/home/oracle/backup/recv/AORD_PITR_D041/datafile/o1_mf_mdata_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /export/home/oracle/backup/DB_d041_31ok0nnt_1_1
channel ORA_AUX_DISK_1: piece handle=/export/home/oracle/backup/DB_d041_31ok0nnt_1_1 tag=BACKUP_WHOLE_LEVEL_01
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 17-SEP-13
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=826408454 file name=/export/home/oracle/backup/recv/AORD_PITR_D041/datafile/o1_mf_mdata_93k0kbjl_.dbf
contents of Memory Script:
{
# set requested point in time
set until  time “TO_DATE(’09/16/13 16:34:47′,’MM/DD/YY HH24:MI:SS’)”;
# online the datafiles restored or switched
sql clone “alter database datafile  5 online”;
# recover and open resetlogs
recover clone database tablespace  “MDATA”, “SYSTEM”, “UNDOTBS1”, “SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile  5 online
Starting recover at 17-SEP-13
using channel ORA_AUX_DISK_1
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=16
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=18
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=19
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=21
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=22
channel ORA_AUX_DISK_1: reading from backup piece /export/home/oracle/backup/archive_d041_826303083_104.arc
channel ORA_AUX_DISK_1: piece handle=/export/home/oracle/backup/archive_d041_826303083_104.arc tag=TAG20130916T163802
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
archived log file name=/export/home/oracle/backup/recv/1_16_826154360.dbf thread=1 sequence=16
channel clone_default: deleting archived log(s)
archived log file name=/export/home/oracle/backup/recv/1_16_826154360.dbf RECID=151 STAMP=826408484
archived log file name=/export/home/oracle/backup/recv/1_17_826154360.dbf thread=1 sequence=17
channel clone_default: deleting archived log(s)
archived log file name=/export/home/oracle/backup/recv/1_17_826154360.dbf RECID=156 STAMP=826408486
archived log file name=/export/home/oracle/backup/recv/1_18_826154360.dbf thread=1 sequence=18
channel clone_default: deleting archived log(s)
archived log file name=/export/home/oracle/backup/recv/1_18_826154360.dbf RECID=157 STAMP=826408487
archived log file name=/export/home/oracle/backup/recv/1_19_826154360.dbf thread=1 sequence=19
channel clone_default: deleting archived log(s)
archived log file name=/export/home/oracle/backup/recv/1_19_826154360.dbf RECID=153 STAMP=826408485
archived log file name=/export/home/oracle/backup/recv/1_20_826154360.dbf thread=1 sequence=20
channel clone_default: deleting archived log(s)
archived log file name=/export/home/oracle/backup/recv/1_20_826154360.dbf RECID=152 STAMP=826408485
archived log file name=/export/home/oracle/backup/recv/1_21_826154360.dbf thread=1 sequence=21
channel clone_default: deleting archived log(s)
archived log file name=/export/home/oracle/backup/recv/1_21_826154360.dbf RECID=154 STAMP=826408485
archived log file name=/export/home/oracle/backup/recv/1_22_826154360.dbf thread=1 sequence=22
channel clone_default: deleting archived log(s)
archived log file name=/export/home/oracle/backup/recv/1_22_826154360.dbf RECID=155 STAMP=826408485
media recovery complete, elapsed time: 00:00:10
Finished recover at 17-SEP-13
database opened
contents of Memory Script:
{
# create directory for datapump import
sql “create or replace directory TSPITR_DIROBJ_DPDIR as ”
/export/home/oracle/backup/recv/dump””;
# create directory for datapump export
sql clone “create or replace directory TSPITR_DIROBJ_DPDIR as ”
/export/home/oracle/backup/recv/dump””;
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/export/home/oracle/backup/recv/dump”
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/export/home/oracle/backup/recv/dump”
Performing export of tables…
EXPDP> Starting “SYS”.”TSPITR_EXP_aorD_aofB”:
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/IDENTITY_COLUMN
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported “MTEST”.”EMP”                               5.507 KB       5 rows
EXPDP> Master table “SYS”.”TSPITR_EXP_aorD_aofB” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_aorD_aofB is:
EXPDP>   /export/home/oracle/backup/recv/dump/tspitr_aorD_84104.dmp
EXPDP> Job “SYS”.”TSPITR_EXP_aorD_aofB” successfully completed at Tue Sep 17 22:12:39 2013 elapsed 0 00:13:17
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_aorD_aice” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_aorD_aice”:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported “MTEST”.”EMP”                               5.507 KB       5 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Removing automatic instance
Automatic instance removed
auxiliary instance file /export/home/oracle/backup/recv/D041/datafile/o1_mf_temp_93k0cm8t_.tmp deleted
auxiliary instance file /export/home/oracle/backup/recv/AORD_PITR_D041/onlinelog/o1_mf_3_93k0m2xo_.log deleted
auxiliary instance file /export/home/oracle/backup/recv/AORD_PITR_D041/onlinelog/o1_mf_2_93k0m0kr_.log deleted
auxiliary instance file /export/home/oracle/backup/recv/AORD_PITR_D041/onlinelog/o1_mf_1_93k0lwoj_.log deleted
auxiliary instance file /export/home/oracle/backup/recv/AORD_PITR_D041/datafile/o1_mf_mdata_93k0kbjl_.dbf deleted
auxiliary instance file /export/home/oracle/backup/recv/D041/datafile/o1_mf_sysaux_93k04m4b_.dbf deleted
auxiliary instance file /export/home/oracle/backup/recv/D041/datafile/o1_mf_undotbs1_93k07t1b_.dbf deleted
auxiliary instance file /export/home/oracle/backup/recv/D041/datafile/o1_mf_system_93k069q1_.dbf deleted
auxiliary instance file /export/home/oracle/backup/recv/D041/controlfile/o1_mf_93jzvxyf_.ctl deleted
auxiliary instance file tspitr_aorD_84104.dmp deleted
Finished recover at 17-SEP-13
RMAN> exit
Recovery Manager complete.
ora12crac1:/export/home/oracle: exit
script done on Tue Sep 17 22:21:07 2013
 
SQL> set line 200
SQL> select * from mtest.emp;
EMPNO ENAME
———- ———————-
1 MALU
2 RAM
3 SAM
4 TOM
5 PAM
 
Auxiliary instance File Listing During the Recovery
ora12crac1:/export/home/oracle: ls -ltrhR /export/home/oracle/backup/recv
/export/home/oracle/backup/recv:
total 6800
-rw-r–r–   1 oracle   oinstall    1.9K Sep 15 11:59 tab_rec.log
drwxr-x—   5 oracle   oinstall     512 Sep 16 17:33 D041
-rw-r—–   1 oracle   oinstall     78K Sep 17 21:50 1_16_826154360.dbf
-rw-r—–   1 oracle   oinstall     39K Sep 17 21:50 1_20_826154360.dbf
-rw-r—–   1 oracle   oinstall     22K Sep 17 21:50 1_19_826154360.dbf
-rw-r—–   1 oracle   oinstall     20K Sep 17 21:50 1_21_826154360.dbf
-rw-r—–   1 oracle   oinstall    2.5K Sep 17 21:50 1_22_826154360.dbf
-rw-r—–   1 oracle   oinstall    1.5K Sep 17 21:50 1_17_826154360.dbf
-rw-r—–   1 oracle   oinstall    3.1M Sep 17 21:50 1_18_826154360.dbf
drwxr-xr-x   2 oracle   oinstall     512 Sep 17 21:50 dump
 
Aux instance deleted the restored archive files below and export dump file got created.
ora12crac1:/export/home/oracle: ls -ltrhR /export/home/oracle/backup/recv
/export/home/oracle/backup/recv:
total 10
-rw-r–r–   1 oracle   oinstall    1.9K Sep 15 11:59 tab_rec.log
drwxr-x—   5 oracle   oinstall     512 Sep 16 17:33 D041
drwxr-x—   4 oracle   oinstall     512 Sep 17 21:55 AORD_PITR_D041
drwxr-xr-x   2 oracle   oinstall     512 Sep 17 21:59 dump
/export/home/oracle/backup/recv/D041:
total 6
drwxr-x—   2 oracle   oinstall     512 Sep 16 17:33 onlinelog
drwxr-x—   2 oracle   oinstall     512 Sep 17 21:42 controlfile
drwxr-x—   2 oracle   oinstall     512 Sep 17 21:51 datafile
/export/home/oracle/backup/recv/D041/onlinelog:
total 0
/export/home/oracle/backup/recv/D041/controlfile:
total 20336
-rw-r—–   1 oracle   oinstall    9.9M Sep 17 22:01 o1_mf_93jzvxyf_.ctl
/export/home/oracle/backup/recv/D041/datafile:
total 2973376
-rw-r—–   1 oracle   oinstall    200M Sep 17 21:55 o1_mf_undotbs1_93k07t1b_.dbf
-rw-r—–   1 oracle   oinstall     20M Sep 17 21:58 o1_mf_temp_93k0cm8t_.tmp
-rw-r—–   1 oracle   oinstall    700M Sep 17 22:00 o1_mf_system_93k069q1_.dbf
-rw-r—–   1 oracle   oinstall    550M Sep 17 22:00 o1_mf_sysaux_93k04m4b_.dbf
/export/home/oracle/backup/recv/AORD_PITR_D041:
total 4
drwxr-x—   2 oracle   oinstall     512 Sep 17 21:54 datafile
drwxr-x—   2 oracle   oinstall     512 Sep 17 21:55 onlinelog
/export/home/oracle/backup/recv/AORD_PITR_D041/datafile:
total 10272
-rw-r—–   1 oracle   oinstall    5.0M Sep 17 21:55 o1_mf_mdata_93k0kbjl_.dbf
/export/home/oracle/backup/recv/AORD_PITR_D041/onlinelog:
total 307488
-rw-r—–   1 oracle   oinstall     50M Sep 17 21:55 o1_mf_2_93k0m0kr_.log
-rw-r—–   1 oracle   oinstall     50M Sep 17 21:55 o1_mf_3_93k0m2xo_.log
-rw-r—–   1 oracle   oinstall     50M Sep 17 22:00 o1_mf_1_93k0lwoj_.log
/export/home/oracle/backup/recv/dump:
total 288
-rw-r—–   1 oracle   oinstall    136K Sep 17 22:12 tspitr_aorD_84104.dmp
 
Verified the SGA_TARGET  which was changed for AUX instance, to avoid memory issues during recovery.
ora12crac1:/export/home/oracle: ps -ef |grep pmon
oracle   832     1   0 20:53:36 ?           0:01 ora_pmon_d041
oracle  1505     1   0 21:53:47 ?           0:00 ora_pmon_aorD
oracle  1725  1271   0 22:00:27 pts/4       0:00 grep pmon
ora12crac1:/export/home/oracle:
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,DATABASE_ROLE,LOG_MODE,PROTECTION_MODE from v$database;
NAME      DB_UNIQUE_NAME                 CONTROL DATABASE_ROLE    LOG_MODE     PROTECTION_MODE
——— —————————— ——- —————- ———— ——————–
D041      aorD_pitr_D041                 CLONE   PRIMARY          NOARCHIVELOG MAXIMUM PERFORMANCE
 
set line 200
col name for a95
select name,status  from v$datafile;
NAME                                                                                            STATUS
———————————————————————————————– ——-
/export/home/oracle/backup/recv/D041/datafile/o1_mf_system_93k069q1_.dbf                        SYSTEM
/export/home/oracle/backup/recv/D041/datafile/o1_mf_sysaux_93k04m4b_.dbf                        ONLINE
/export/home/oracle/backup/recv/D041/datafile/o1_mf_undotbs1_93k07t1b_.dbf                      ONLINE
/u02/oradata/d041/users01.dbf                                                                   OFFLINE
/export/home/oracle/backup/recv/AORD_PITR_D041/datafile/o1_mf_mdata_93k0kbjl_.dbf               ONLINE
 
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 352M
 
SQL> show parameter pga_aggregate_limit
NAME                                 TYPE        VALUE
———————————— ———– ——————————
pga_aggregate_limit                  big integer 1807M
 
Export Datapump Progress monitor in AUX instance.
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
— locate Data Pump jobs:
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE ‘BIN$%’
ORDER BY 1,2;
OWNER_NAME JOB_NAME             OPERATION   JOB_MODE    STATE       ATTACHED_SESSIONS
———- ——————– ———– ———– ———– —————–
SYS        TSPITR_EXP_aorD_aofB EXPORT      TABLE       EXECUTING                   1
 
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid,s.status, s.username, d.job_name, p.spid, s.serial#, p.pid from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr;
DATE                PROGRAM                                    SID STATUS   USERNAME   JOB_NAME             SPID    SERIAL#     PID
——————- ————————————– ——- ——– ———- ——————– ——- ——- ——-
2013-09-17 22:10:57 rman@ora12crac1 (TNS V1-V3)                  1 ACTIVE   SYS        TSPITR_EXP_aorD_aofB 1565          9      18
2013-09-17 22:10:57 oracle@ora12crac1 (DW00)                    43 ACTIVE   SYS        TSPITR_EXP_aorD_aofB 1692         19      21
2013-09-17 22:10:57 oracle@ora12crac1 (DM00)                    51 ACTIVE   SYS        TSPITR_EXP_aorD_aofB 1690         41      31
 
SQL> select sid, serial#, sofar, totalwork
from v$session_longops;
SID SERIAL#   SOFAR TOTALWORK
——- ——- ——- ———
1       9  193493    193494
1       9       1         1
1       9      82        82
1       9      41        41
1       9       3         3
1       9       7         7
1       9       0         0
1       9       0         0
1       9       2         2
1       9       0         0
1       9       0         0
1       9       0         0
51      41       0         1
43      19     259       259
14 rows selected.
 
col OWNER.OBJECT for a50
SELECT o.status, o.object_id, o.object_type,
o.owner||’.’||object_name “OWNER.OBJECT”
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE ‘BIN$%’ ORDER BY 4,2;
STATUS  OBJECT_ID OBJECT_TYPE             OWNER.OBJECT
——- ——— ———————– ————————————————–
VALID       19893 TABLE                   SYS.TSPITR_EXP_aorD_aofB
 
 
Few Errors faced During the Recovery due to fewer resources like memory.
 
starting up automatic instance D041
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2013 11:54:46
RMAN-04006: error from auxiliary database: ORA-12549: TNS:operating system resource quota exceeded
 
alert.log
=============
CLI notifier numLatches:3 maxDescs:156
Sun Sep 15 11:53:53 2013
All SGA segments are allocated at startup
Sun Sep 15 11:53:55 2013
WARNING: Not enough physical memory for SHM_SHARE_MMU segment of size 0x000000003f400000 [flag=0x4000]
LICENSE_MAX_SESSION = 0
 
Download PDF Format
 
Advertisements

From → 12c

Leave a Comment

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

%d bloggers like this: