Oracle Study之--基于ASM的TSPITR(基于表空间的完全恢复)

  1. 云栖社区>
  2. 博客>
  3. 正文

Oracle Study之--基于ASM的TSPITR(基于表空间的完全恢复)

余二五 2017-11-15 15:19:00 浏览549
展开阅读全文

系统环境:

操作系统:AIX5.3-08

数据库:  Oracle 10gR2

Understanding RMAN TSPITR

In order to use TSPITR effectively, you need to understand what problems it can solve for you, what the major elements used in TSPITR are, what RMAN does during TSPITR, and limitations on when and how it can be applied.

RMAN TSPITR Concepts

Figure 8-1, "Tablespace Point-in-Time Recovery (TSPITR) Architecture" illustrates the context within which TSPITR takes place, and a general outline of the process.

Figure 8-1 Tablespace Point-in-Time Recovery (TSPITR) Architecture

wKiom1TIXrnRCEMWAAGW05m6DzE496.jpg


Description of "Figure 8-1 Tablespace Point-in-Time Recovery (TSPITR) Architecture"

The figure contains the following entities:

  • The target instance, containing the tablespace to be recovered

  • The Recovery Manager client

  • The control file and (optional) recovery catalog, used for the RMAN repository records of backup activity

  • Archived redo logs and backup sets from the target database, which are the source of the reconstructed tablespace.

  • The auxiliary instance, an Oracle database instance used in the recovery process to perform the actual work of recovery.

There are four other important terms related to TSPITR, which will be used in the rest of this discussion:

  • The target time, the point in time or SCN that the tablespace will be left at after TSPITR

  • The recovery set, which consists of the datafiles containing the tablespaces to be recovered;

  • The auxiliary set, which includes datafiles required for TSPITR of the recovery set which are not themselves part of the recovery set. The auxiliary set typically includes:

    The auxiliary instance has other files associated with it, such as a control file, parameter file, and online logs, but they are not part of the auxiliary set.

    • A copy of the SYSTEM tablespace

    • Datafiles containing rollback or undo segments from the target instance

    • In some cases, a temporary tablespace, used during the export of database objects from the auxiliary instance

  • The auxiliary destination, an optional location on disk which can be used to store any of the auxiliary set datafiles, control files and online logs of the auxiliary instance during TSPITR. Files stored here can be deleted after TSPITR is complete.

Performing Basic RMAN TSPITR

Having selected your tablespaces to recover and your target time, you are now ready to perform RMAN TSPITR. You have a few different options available to you:

  • Fully automated TSPITR--in which you specify an auxiliary destination and let RMAN manage all aspects of the TSPITR. This is the simplest way to perform TSPITR, and is recommended unless you specifically need more control over the location of recovery set files after TSPITR or auxiliary set files during TSPITR, or control over the channel configurations or some other aspect of your auxiliary instance.

  • Customized TSPITR with an automatic auxiliary instance--in which you base your TSPITR on the behavior of fully automated TSPITR, possibly still using an auxiliary destination, but customize one or more aspects of the behavior, such as the location of auxiliary set or recovery set files, or specifying initialization parameters or channel configurations for the auxiliary instance created and managed by RMAN.

  • TSPITR with your own auxiliary instance--in which you take responsibility for setting up, starting, stopping and cleaning up the auxiliary instance used in TSPITR, and possibly also manage the TSPITR process using some of the methods available in customized TSPITR with an automatic auxiliary instance.

案例分析:

    用户在过去的时间点对emp1表做了误操作,需要通过物理备份恢复到过去的数据,本案例采用基于表空间的不完全恢复来实现对表的恢复!

1、案例测试环境

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
SQL> show parameter name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      zsdb
db_unique_name                       string      zsdb
global_names                         boolean     FALSE
instance_name                        string      zsdb
lock_name_space                      string
log_file_name_convert                string
service_names                        string      zsdb
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/zsdb/datafile/system.257.870003801
+DG1/zsdb/datafile/undotbs1.260.870004111
+DG1/zsdb/datafile/sysaux.258.870003999
+DG1/zsdb/datafile/users.262.870004141
+DG1/zsdb/datafile/zstb.259.870004085
+DG1/zsdb/datafile/testtb.261.870004125
6 rows selected.
SQL> col member for a50
SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- --------------------------------------------------
         4 +DG1/zsdb/onlinelog/group_4.266.870004663
         4 +RCY1/zsdb/onlinelog/group_4.256.870004669
         5 +DG1/zsdb/onlinelog/group_5.267.870004689
         5 +RCY1/zsdb/onlinelog/group_5.257.870004693
SQL> set linesize 120
SQL> r
  1* select file_id,file_name,tablespace_name from dba_data_files
   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
         4 +DG1/zsdb/datafile/users.262.870004141             USERS
         3 +DG1/zsdb/datafile/sysaux.258.870003999            SYSAUX
         2 +DG1/zsdb/datafile/undotbs1.260.870004111          UNDOTBS1
         1 +DG1/zsdb/datafile/system.257.870003801            SYSTEM
         5 +DG1/zsdb/datafile/zstb.259.870004085              ZSTB
         6 +DG1/zsdb/datafile/testtb.261.870004125            TESTTB
6 rows selected.

2、建立测试

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
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TB_EMP                         TABLE
TB_DEPT                        TABLE
TB1                            TABLE
EMP1                           TABLE
8 rows selected.
 
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS
TB_EMP                         TESTTB
TB_DEPT                        TESTTB
TB1                            USERS
EMP1                           ZSTB
8 rows selected.
 
10:43:58 SQL> insert into emp1 select * from emp;
14 rows created.
 
10:44:10 SQL> commit;
Commit complete.
 
10:44:12 SQL> select * from emp1;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
15 rows selected.
 
10:44:34 SQL> update emp1 set sal=9000 ;    
15 rows updated.
 
10:44:47 SQL> commit;
Commit complete.
 
10:44:49 SQL> select * from emp1;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 SMITH      CLERK           7902 17-DEC-80       9000                    20
      7369 SMITH      CLERK           7902 17-DEC-80       9000                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       9000        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       9000        500         30
      7566 JONES      MANAGER         7839 02-APR-81       9000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       9000       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       9000                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       9000                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       9000                    20
      7839 KING       PRESIDENT            17-NOV-81       9000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       9000          0         30
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       9000                    20
      7900 JAMES      CLERK           7698 03-DEC-81       9000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       9000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       9000                    10
15 rows selected.
 
表被误操作前的scn:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
     988659
      
10:44:54 SQL> truncate table emp1 ;
Table truncated.
 
10:46:28 SQL> select * from emp1;
no rows selected
 
10:46:33 SQL> insert into emp1 select * from emp where rownum=1;
1 row created.
 
10:46:45 SQL> update emp1 set empno=9999;
1 row updated.
 
10:46:53 SQL> commit;
Commit complete.
 
10:46:56 SQL> select * from emp1;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 SMITH      CLERK           7902 17-DEC-80        800                    20

3、检查表空间自包含

1
2
3
4
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('ZSTB',TRUE);
PL/SQL procedure successfully completed.
SQL> select * from TRANSPORT_SET_VIOLATIONS t;
no rows selected

4、通过rman做TSPITR

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
[10:49:12 oracle@aix217: dbs]$mkdir /home/oracle/zsdb
[10:49:23 oracle@aix217: dbs]$cd /home/oracle/zsdb
[10:49:30 oracle@aix217: zsdb]$rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 28 10:49:35 2015
Copyright (c) 19822005, Oracle.  All rights reserved.
connected to target database: ZSDB (DBID=3945368821)
RMAN>  recover tablespace zstb until scn 988659
2> ;
Starting recover at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=125 devtype=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='pgoi'
initialization parameters used for automatic instance:
db_name=ZSDB
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ZSDB_pgoi
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
#No auxiliary destination in use
#Use default controlfile
starting up automatic instance ZSDB
Oracle instance started
Total System Global Area     205520896 bytes
Fixed Size                     2019680 bytes
Variable Size                146804384 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6365184 bytes
Automatic instance created
Removing automatic instance
shutting down automatic instance 
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2015 10:50:09
RMAN-05002: aborting point-in-time tablespace recovery
RMAN-05517: temporary file +DG1/zsdb/tempfile/temp.265.870004489 conflicts with file used by target database
RMAN-05001: auxiliary filename +DG1/zsdb/datafile/undotbs1.260.870004111 conflicts with a file used by the target database
RMAN-05001: auxiliary filename +DG1/zsdb/datafile/system.257.870003801 conflicts with a file used by the target database

---在做TSPITR时,需要建立Auxiliary Instance,需要转储system、undotbs,应该将Auxiliary Instance的datafile与原库存储到不同的diskgroup。

5、建立ASM diskgroup

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
[11:05:43 root@aix217: /]#lsvg rootvg
VOLUME GROUP:       rootvg                   VG IDENTIFIER:  0000c9a20000d700000001447571be5b
VG STATE:           active                   PP SIZE:        128 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      546 (69888 megabytes)
MAX LVs:            256                      FREE PPs:       85 (10880 megabytes)
LVs:                14                       USED PPs:       461 (59008 megabytes)
OPEN LVs:           12                       QUORUM:         2 (Enabled)
TOTAL PVs:          1                        VG DESCRIPTORS: 2
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         1                        AUTO ON:        yes
MAX PPs per VG:     32512                                     
MAX PPs per PV:     1016                     MAX PVs:        32
LTG size (Dynamic): 256 kilobyte(s)          AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable 
 
[11:06:27 root@aix217: /]#mklv -y 'lv_aux' -t 'raw' rootvg 30
lv_aux
 
[11:07:39 root@aix217: /]#ls -l /dev |grep aux
brw-rw----    1 root     system       1015 Jan 28 11:07 lv_aux
brw-rw----    1 oracle   dba          70,  7 Jan 14 16:26 rac_sysaux
crw-rw----    1 root     system       1015 Jan 28 11:07 rlv_aux
crw-rw----    1 oracle   dba          70,  7 Jan 14 16:26 rrac_sysaux
 
[11:07:50 root@aix217: /]#chown oracle:dba /dev/*lv_aux
[11:08:21 root@aix217: /]#chmod 660 /dev/*lv_aux
[11:08:29 root@aix217: /]#ls -l /dev |grep aux
brw-rw----    1 oracle   dba          1015 Jan 28 11:07 lv_aux
crw-rw----    1 oracle   dba          1015 Jan 28 11:07 rlv_aux
 
SQL> create diskgroup dg2 external redundancy
  2  disk '/dev/rlv_aux';
Diskgroup created.
SQL> select name ,state from v$asm_diskgroup;
NAME                           STATE
------------------------------ -----------
DG1                            MOUNTED
RCY1                           MOUNTED
DG2                            MOUNTED

6、再次做TSPITR

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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
[11:13:58 oracle@aix217: ~]$rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 28 11:14:07 2015
Copyright (c) 19822005, Oracle.  All rights reserved.
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database: 
ORA-29701: unable to connect to Cluster Manager
connected to target database: ZSDB (DBID=3945368821)
RMAN> RUN
2> {
3>   SET NEWNAME FOR DATAFILE 1 to '+dg2';
4>    SET NEWNAME FOR DATAFILE 2 to '+dg2';
5>   RECOVER TABLESPACE zstb until scn 988659
6>      AUXILIARY DESTINATION '+DG2';
}
7
executing command: SET NEWNAME
using target database control file instead of recovery catalog
executing command: SET NEWNAME
Starting recover at 28-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=125 devtype=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='qauC'
initialization parameters used for automatic instance:
db_name=ZSDB
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ZSDB_qauC
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=+DG2
control_files=+DG2/cntrl_tspitr_ZSDB_qauC.f
 
 
starting up automatic instance ZSDB
Oracle instance started
Total System Global Area     205520896 bytes
Fixed Size                     2019680 bytes
Variable Size                146804384 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6365184 bytes
Automatic instance created
contents of Memory Script:
{
# set the until clause
set until  scn 988659;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script
executing command: SET until clause
Starting restore at 28-JAN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=33 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /backup/backupctl_ZSDB_ncptrifa_1_1.ctl
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/backupctl_ZSDB_ncptrifa_1_1.ctl tag=BKCTL
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:38
output filename=+DG2/cntrl_tspitr_zsdb_qauc.f
Finished restore at 28-JAN-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  scn 988659;
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'ZSTB' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set a destination filename for restore
set newname for datafile  1 to 
 "+DG2";
# set a destination filename for restore
set newname for datafile  2 to 
 "+DG2";
# set an omf destination tempfile
set newname for clone tempfile  2 to new;
# set a destination filename for restore
set newname for datafile  5 to 
 "+DG1/zsdb/datafile/zstb.259.870004085";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  125;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  5 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "ZSTB""SYSTEM""UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace ZSTB offline for recover
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed temporary file 2 to +DG2 in control file
Starting restore at 28-JAN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DG2
restoring datafile 00005 to +DG1/zsdb/datafile/zstb.259.870004085
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc0_ZSDB_mbptna6f_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc0_ZSDB_maptna6d_1_1 tag=INC0
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 28-JAN-15
datafile 1 switched to datafile copy
input datafile copy recid=48 stamp=870175071 filename=+DG2/tspitr_zsdb_qauc/datafile/system.258.870175007
datafile 2 switched to datafile copy
input datafile copy recid=49 stamp=870175071 filename=+DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973
sql statement: alter database datafile  1 online
sql statement: alter database datafile  2 online
sql statement: alter database datafile  5 online
Starting recover at 28-JAN-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_mhptnb2j_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_mhptnb2j_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085
destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_miptnb2l_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_miptnb2l_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_moptnbuk_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_moptnbuk_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085
destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_mpptnbum_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_mpptnbum_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_mvptncqp_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_mvptncqp_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085
destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_n0ptncqr_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_n0ptncqr_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DG2/tspitr_zsdb_qauc/datafile/system.258.870175007
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_n6ptridv_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_n6ptridv_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting incremental datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: +DG1/zsdb/datafile/zstb.259.870004085
destination for restore of datafile 00002: +DG2/tspitr_zsdb_qauc/datafile/undotbs1.257.870174973
channel ORA_AUX_DISK_1: reading from backup piece /backup/inc1_ZSDB_n7ptrie1_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/inc1_ZSDB_n7ptrie1_1_1 tag=INC1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=181
channel ORA_AUX_DISK_1: reading from backup piece /backup/arch_ZSDB_nbptriev_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/arch_ZSDB_nbptriev_1_1 tag=BACKCH
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_181_869671369.dbf thread=1 sequence=181
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_181_869671369.dbf recid=568 stamp=870175096
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=182
channel ORA_AUX_DISK_1: reading from backup piece /backup/arch_ZSDB_nhptrjca_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/backup/arch_ZSDB_nhptrjca_1_1 tag=BACKCH
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_182_869671369.dbf thread=1 sequence=182
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db_1/dbs/arch1_182_869671369.dbf recid=569 stamp=870175100
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-JAN-15
database opened
contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/product/10.2.0/db_1/bin/oracle\)\(ARGV0=oracleqauC\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=qauC^'\)\)\(CONNECT_DATA=\(SID=qauC\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
 ZSTB file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace  ZSTB online";
sql "alter tablespace  ZSTB offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
 
从Auxiliary Instance导出恢复后的数据:
Export: Release 10.2.0.1.0 - Production on Wed Jan 28 11:18:41 2015
Copyright (c) 19822005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export Tablespace Point-in-time Recovery objects...
For tablespace ZSTB ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                           EMP1
EXP-00091: Exporting questionable statistics.
. . exporting table                           TEST
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
host command complete
database closed
database dismounted
Oracle instance shut down
Import: Release 10.2.0.1.0 - Production on Wed Jan 28 11:19:25 2015
Copyright (c) 19822005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
 
导入恢复后的数据到主库:
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table                         "EMP1"
. importing TEST's objects into TEST
. . importing table                         "TEST"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete
sql statement: alter tablespace  ZSTB online
sql statement: alter tablespace  ZSTB offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file +DG2/cntrl_tspitr_zsdb_qauc.f deleted
Removing automatic instance
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/28/2015 11:19:50
ORA-15045: ASM file name '+DG2' is not in reference form

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
错误信息:
    在recovery完成后,会删除Auxiliary Instance的文件,因为文件放在ASM diskgroup下,故删除失败,可以考虑将Auxiliary Instance文件放在文件系统下。
    [11:32:21 oracle@aix217: ~]$asmcmd
ASMCMD> ls
DG1/
DG2/
RCY1/
ASMCMD> cd dg2
ASMCMD> ls
TSPITR_ZSDB_QAUC/
ASMCMD> cd TSPITR_ZSDB_QAUC/
ASMCMD> ls
DATAFILE/
ONLINELOG/
TEMPFILE/
ASMCMD>
 
恢复脚本:        
RUN
 {
   SET NEWNAME FOR DATAFILE 1 to '+dg2';
   SET NEWNAME FOR DATAFILE 2 to '+dg2';
   RECOVER TABLESPACE zstb until scn 988659
   AUXILIARY DESTINATION '/home/oracle/zsdb';
}


7、验证数据恢复

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
SQL> conn scott/tiger
Connected.
SQL> select * from emp1;
select * from emp1
              *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5'+DG1/zsdb/datafile/zstb.259.870004085'
SQL> conn /as sysdba
Connected.
SQL> alter database datafile 5 online;
Database altered.
SQL> conn scott/tiger
Connected.
SQL> select * from emp1;
select * from emp1
              *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5'+DG1/zsdb/datafile/zstb.259.870004085'
 
SQL> alter tablespace zstb online;
Tablespace altered.
SQL> set linesize 120
SQL> r
  1* select * from scott.emp1
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      9999 SMITH      CLERK           7902 17-DEC-80       9000                    20
      7369 SMITH      CLERK           7902 17-DEC-80       9000                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       9000        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       9000        500         30
      7566 JONES      MANAGER         7839 02-APR-81       9000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       9000       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       9000                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       9000                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       9000                    20
      7839 KING       PRESIDENT            17-NOV-81       9000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       9000          0         30
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       9000                    20
      7900 JAMES      CLERK           7698 03-DEC-81       9000                    30
      7902 FORD       ANALYST         7566 03-DEC-81       9000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       9000                    10
15 rows selected.

    @至此,emp1表中被误删除的数据被恢复完成!


      通过RMAN做基于表空间的不完全恢复(TSPTIR),可以在不关闭数据库的情况下快速恢复因为用户误操作而丢失的数据;相比传统的基于整个库的不完全恢复,提高了数据库的可用性和恢复的效率。










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1609109,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注