Oracle Database 12c: RMAN recover table


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SQL> create table anar_rc (field1 varchar2(50) primary key);

Table created.

SQL> insert into anar_rc values ('test');

1 row created.

SQL> insert into anar_rc values ('foo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from anar_rc;

FIELD1
--------------------------------------------------
foo
test

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
803916

SQL> drop table anar_rc;

Table dropped.

SQL> purge table anar_rc;

Table purged.

SQL> flashback table purge to before drop;
flashback table purge to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
 
You can anar_rcver the table with:
1
RMAN> anar_rcver table ludovico.anar_rc until scn 803916 auxiliary destination '/tmp/anar_rcver';
 
You identify the schema.table:partition to restore, optionally you can pass the pluggable database containing the table to anar_rcver, the time definition as usual (scn, seq# or timestamp) and an auxiliary destination.
This Auxiliary destination is well-known to be mandatory for TSPITR. You can pass other options like table renaming or tablespace remapping.
Off course, the database must be open in read-write, in archivelog mode and at least one successful backup must be taken.
How it works
Oracle prepare an auxiliary instance by restoring the SYSTEM, UNDO and SYSAUX tablespaces.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
Starting anar_rcver at 10-JUN-13
using channel ORA_DISK_1
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='kCoq'

initialization parameters used for automatic instance:
db_name=CLASSIC
db_unique_name=kCoq_pitr_CLASSIC
compatible=12.0.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/tmp/anar_rcver
log_archive_dest_1='location=/tmp/anar_rcver'
#No auxiliary parameter file used

starting up automatic instance CLASSIC

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 281020272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until scn 803916;
# 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 10-JUN-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: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
output file name=/tmp/anar_rcver/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl
Finished restore at 10-JUN-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 scn 803916;
# set destinations for anar_rcvery 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 anar_rcvery 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 /tmp/anar_rcver/CLASSIC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-JUN-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/anar_rcver/CLASSIC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/anar_rcver/CLASSIC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/anar_rcver/CLASSIC/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56
Finished restore at 10-JUN-13

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=817751486 file name=/tmp/anar_rcver/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=817751486 file name=/tmp/anar_rcver/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=817751487 file name=/tmp/anar_rcver/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf
Then it opens in READ-ONLY mode the partial database.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
contents of Memory Script:
{
# set requested point in time
set until scn 803916;
# 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";
# anar_rcver and open database read only
anar_rcver 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 anar_rcver at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media anar_rcvery

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=54
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=55
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
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=/tmp/anar_rcver/1_54_814717091.dbf thread=1 sequence=54
archived log file name=/tmp/anar_rcver/1_55_814717091.dbf thread=1 sequence=55
media anar_rcvery complete, elapsed time: 00:00:10
Finished anar_rcver at 10-JUN-13

sql statement: alter database open read only
 
It uses then the read-only dictionary to take the tablespace that was containing the table before the data loss. This tablespace (users in my example) is restored and anar_rcvered, and the database is opened.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
contents of Memory Script:

{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/tmp/anar_rcver/CLASSIC/controlfile/o1_mf_8vctyxcy_.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 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/tmp/anar_rcver/CLASSIC/controlfile/o1_mf_8vctyxcy_.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 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until scn 803916;
# set destinations for anar_rcvery set and auxiliary set datafiles
set newname for datafile 4 to new;
# restore the tablespaces in the anar_rcvery set and the auxiliary set
restore clone datafile 4;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 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 00004 to /tmp/anar_rcver/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 10-JUN-13

datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=817751583 file name=/tmp/anar_rcver/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 803916;
# online the datafiles restored or switched
sql clone "alter database datafile 4 online";
# anar_rcver and open resetlogs
anar_rcver 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 4 online

Starting anar_rcver at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media anar_rcvery

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=54
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=55
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
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=/tmp/anar_rcver/1_54_814717091.dbf thread=1 sequence=54
channel clone_default: deleting archived log(s)
archived log file name=/tmp/anar_rcver/1_54_814717091.dbf RECID=4 STAMP=817751585
archived log file name=/tmp/anar_rcver/1_55_814717091.dbf thread=1 sequence=55
channel clone_default: deleting archived log(s)
archived log file name=/tmp/anar_rcver/1_55_814717091.dbf RECID=5 STAMP=817751587
media anar_rcvery complete, elapsed time: 00:00:01
Finished anar_rcver at 10-JUN-13

database opened
 
At this  point, RMAN starts an export/import with datapump to move the table from the auxiliary database back to the target database:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/anar_rcver''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/anar_rcver''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/anar_rcver''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/anar_rcver''

Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_kCoq_fqic":
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/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "LUDOVICO"."ANAR_RC" 5.054 KB 2 rows
EXPDP> Master table "SYS"."TSPITR_EXP_kCoq_fqic" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is:
EXPDP> /tmp/anar_rcver/tspitr_kCoq_82218.dmp
EXPDP> Job "SYS"."TSPITR_EXP_kCoq_fqic" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41
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_kCoq_Fbti" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_kCoq_Fbti":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "LUDOVICO"."ANAR_RC" 5.054 KB 2 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_kCoq_Fbti" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07
Import completed
 
Finally, the auxiliary instance is cleaned:
1
2
3
4
5
6
7
8
9
10
11
12
13
Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/anar_rcver/CLASSIC/datafile/o1_mf_temp_8vcv5mrt_.tmp deleted
auxiliary instance file /tmp/anar_rcver/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_3_8vcv8nfh_.log deleted
auxiliary instance file /tmp/anar_rcver/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_2_8vcv8fqb_.log deleted
auxiliary instance file /tmp/anar_rcver/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_1_8vcv86fv_.log deleted
auxiliary instance file /tmp/anar_rcver/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf deleted
auxiliary instance file /tmp/anar_rcver/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf deleted
auxiliary instance file /tmp/anar_rcver/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf deleted
auxiliary instance file /tmp/anar_rcver/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf deleted
auxiliary instance file /tmp/anar_rcver/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl deleted
auxiliary instance file tspitr_kCoq_82218.dmp deleted
Finished anar_rcver at 10-JUN-13
 
We can check if our table is ok:
PgSQL
1
2
3
4
5
6
7
8
RMAN> select * from ludovico.anar_rc;

FIELD1
--------------------------------------------------
foo
test

RMAN>
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

%d bloggers like this: