Tablespace-level remote Incomplete recovery in Oracle databases

When the Oracle database is remotely restored, it takes a lot of space and a long time to completely restore the entire database. If the mode-based recovery method is used to restore all things in a certain mode

When the Oracle database is remotely restored, it takes a lot of space and a long time to completely restore the entire database. If the mode-based recovery method is used to restore all things in a certain mode

When the Oracle database is remotely restored, it takes a lot of space and a long time to completely restore the entire database.
If the mode-based recovery method is used to recover only all objects in a certain mode, instead of all the data, this will save a lot of time and space.
This is often used in Oracle 9i and can be used in 10g and 11g versions.
The following example shows how to restore data from a RAC database on ASM to a single instance database on an archive system in a 10 Gb environment, and recover data from only a few users.
The procedure is as follows:
1. Copy the backup group to the machine to be restored. If there is enough space, you can copy the data backup, control the archive backup, and archive backup together. If not, first copy control file backup and data file backup.
2. Close the database and start nomount to restore the control file.
Shutdown immediate;
Startup nomount;
Rman target/
Restore controlfile from '/data/urpdb/urpdb_ctl_ikn3tot4_00001.20120222 ';
3. Modify the control file
Query the tablespace information of the user to restore
Select owner, tablespace_name, count (*) from dba_segments group by owner, tablespace_name;
Find the data files corresponding to these tablespaces and SYSTEM tablespaces ('system', 'sysaux ', 'undotbs1', 'undotbs2', 'undotbs3')
Select *
From dba_data_files
Where tablespace_name in
('Tsp _ urp', 'tsp _ URP_INDEX ', 'tsp _ jc', 'tsp _ JC_INDEX', 'tsp _ GXSJ ',
'Tsp _ GXSJ_INDE ', 'tsp _ owb', 'tsp _ YJSSJCK', 'users', 'system ',
'Sysaux ', 'undotbs1', 'undotbs2', 'undotbs3', 'undotbs3 ');
SQL> alter database backup controlfile to trace;
Modify the control file and execute:
Create controlfile reuse database "URPDB" RESETLOGS FORCE LOGGING ARCHIVELOG
# MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
# MAXLOGHISTORY 9088
LOGFILE
GROUP 1 (
'/Opt/app/oracle/oradata/URPDB/group1_2.log ',
'/Opt/app/oracle/oradata/URPDB/group1_1.log'
) SIZE 10 M,
GROUP 2 (
'/Opt/app/oracle/oradata/URPDB/group2_2.log ',
'/Opt/app/oracle/oradata/URPDB/group2_1.log'
) SIZE 10 M,
GROUP 3 (
'/Opt/app/oracle/oradata/URPDB/group3_2.log ',
'/Opt/app/oracle/oradata/URPDB/group3_1.log'
) SIZE 10 M,
GROUP 11'/opt/app/oracle/oradata/URPDB/group11_1.log 'size 100 M,
GROUP 12'/opt/app/oracle/oradata/URPDB/group12_1.log 'SIZE 100 M,
GROUP 13'/opt/app/oracle/oradata/URPDB/group13_1.log 'size 100 M,
GROUP 14'/opt/app/oracle/oradata/URPDB/group14_1.log 'SIZE 100 M,
GROUP 15'/opt/app/oracle/oradata/URPDB/group15_1.log 'size 100 M
-- STANDBY LOGFILE
DATAFILE
'/Opt/app/oracle/oradata/URPDB/system01.dbf ',
'/Opt/app/oracle/oradata/URPDB/undotbs1.dbf ',
'/Opt/app/oracle/oradata/URPDB/sysaux. dbf ',
'/Opt/app/oracle/oradata/URPDB/users. dbf ',
'/Opt/app/oracle/oradata/URPDB/undotbs2.dbf ',
'/Opt/app/oracle/oradata/URPDB/tsp_urp.dbf ',
'/Opt/app/oracle/oradata/URPDB/tsp_urp_index.dbf ',
'/Opt/app/oracle/oradata/URPDB/tsp_gxsj.dbf ',
'/Opt/app/oracle/oradata/URPDB/tsp_urp.dbf_1.ora ',
'/Opt/app/oracle/oradata/URPDB/tsp_owb.ora ',
'/Opt/app/oracle/oradata/URPDB/tsp_jc.dbf ',
'/Opt/app/oracle/oradata/URPDB/tsp_jc_index.dbf ',
'/Opt/app/oracle/oradata/URPDB/tsp_yjssjck.dbf ',
'/Opt/app/oracle/oradata/URPDB/undotbs03.dbf ',
'/Opt/app/oracle/oradata/URPDB/undotbs04.dbf'
Character set AL32UTF8
;
Mount the database;
SQL> alter database mount;

Select name from v $ datafile;
-- Select 'alter database rename file ''' | name | ''' to ''' | replace (name, '+ urpdbdg ', '/opt/app/oracle/oradata/urpdb') | ''';' from v $ datafile;
3. Copy the backup data file to the test machine.
Rman> catalog start with '/data/urpdb/'; run the following command to write the backup file of the data file in this directory to catalog.
4. restore database
Select file #, name from v $ datafile; --- check the location where the data file is stored in the control file. Here is "+ URPDBDG /... ", replace with the location where the target database file is stored.
Set linesize 300
Column name format a200
Set pagesize 100

Select 'set newname for datafile' | file # | 'to ''' | replace (name,' + URPDBDG ', '/opt/app/oracle/oradata/urpdb') | '''; 'newname from v $ datafile -- if one target directory is insufficient, multiple target directories can be used.
Union all
Select 'Restore datafile' | file # | '; 'newname from v $ datafile;

Select *
From dba_data_files
Where tablespace_name in
('Tsp _ urp', 'tsp _ URP_INDEX ', 'tsp _ jc', 'tsp _ JC_INDEX', 'tsp _ GXSJ ',
'Tsp _ GXSJ_INDE ', 'tsp _ owb', 'tsp _ YJSSJCK', 'users', 'system ',
'Sysaux ', 'undotbs1', 'undotbs2', 'undotbs3', 'undotbs3 ');

Rman>
Run
{
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
Set until time "to_date ('2017-02-23 10:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')";
Set newname for datafile 1 to '/opt/app/oracle/oradata/URPDB/system01.dbf ';
Set newname for datafile 2 to '/opt/app/oracle/oradata/URPDB/undotbs1.dbf ';
Set newname for datafile 3 to '/opt/app/oracle/oradata/URPDB/sysaux. dbf ';
Set newname for datafile 4 to '/opt/app/oracle/oradata/URPDB/users. dbf ';
Set newname for datafile 6 to '/opt/app/oracle/oradata/URPDB/undotbs2.dbf ';
Set newname for datafile 9 to '/opt/app/oracle/oradata/URPDB/tsp_urp.dbf ';
Set newname for datafile 10 to '/opt/app/oracle/oradata/URPDB/tsp_urp_index.dbf ';
Set newname for datafile 14 to '/opt/app/oracle/oradata/URPDB/tsp_gxsj.dbf ';
Set newname for datafile 16 to '/opt/app/oracle/oradata/URPDB/tsp_urp.dbf_1.ora ';
Set newname for datafile 18 to '/opt/app/oracle/oradata/URPDB/tsp_owb.ora ';
Set newname for datafile 19 to '/opt/app/oracle/oradata/URPDB/tsp_jc.dbf ';
Set newname for datafile 20 to '/opt/app/oracle/oradata/URPDB/tsp_jc_index.dbf ';
Set newname for datafile 26 to '/opt/app/oracle/oradata/URPDB/tsp_yjssjck.dbf ';
Set newname for datafile 29 to '/opt/app/oracle/oradata/URPDB/undotbs03.dbf ';
Set newname for datafile 30 to '/opt/app/oracle/oradata/URPDB/undotbs04.dbf ';
Restore datafile 1;
Restore datafile 2;
Restore datafile 3;
Restore datafile 4;
Restore datafile 6;
Restore datafile 9;
Restore datafile 10;
Restore datafile 14;
Restore datafile 16;
Restore datafile 18;
Restore datafile 19;
Restore datafile 20;
Restore datafile 26;
Restore datafile 29;
Restore datafile 30;
Switch datafile all;
Release channel ch01;
Release channel ch02;
}
5. Change the path of the log file and temporary storage file to the path of the target database (which can be performed simultaneously with the restore)
Select member from v $ logfile;

Select name from v $ tempfile;

Select 'alter database rename file ''' | member | ''' to ''' | replace (member, '+ urpdbdg ', '/opt/app/oracle/oradata/urpdb') | ''';' from v $ logfile;

Alter database rename file '+ YWKDG/ywk/onlinelog/group15_1.log' to '/opt/app/oracle/oradata2/YWKDG/group15_1.log ';
.....

Select 'alter database rename file ''' | name | ''' to ''' | replace (name, '+ urpdbdg ', '/opt/app/oracle/oradata2/urpdb/') | '''; 'from v $ tempfile;

Alter database rename file '+ YWKDG/ywk/tempfile/tsp_zc_temp02.dbf' to '/opt/app/oracle/oradata2/YWKDG/tsp_zc_temp02.dbf ';

6. Copy the archive file to the target database server,
Rman> catalog start with '/data/urpdb/'; run the following command to write the backup file of the archive file in this directory to catalog.
Run
{
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
Set until time "to_date ('2017-02-23 10:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')";
Recover database;
Release channel ch01;
Release channel ch02;
}
Or
SQL> select to_char (checkpoint_time, 'yyyy-mm-dd hh24: mi: ss') from v $ datafile;

S ql> select file #, checkpoint_change # from v $ datafile order by checkpoint_change #;

FILE # CHECKPOINT_CHANGE #
----------------------------
14 2793109724
20 2793109725
6 2793232002
19 2793232002
18 2793258889
26 2793258889
9 2793281171
30 2793281171
1 2793283158
16 2793301522
29 2793301522
2 2793304369
10 2793304369
4 2793333238
3 2793333238

15 rows selected.

Run
{
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
Set until scn 2793333238;
Recover database;
Release channel ch01;
Release channel ch02;
}

If the recover database lacks a file, you can check the file in the master database, and copy the file and recover it again.
RMAN> list backupset of archivelog logseq 7438 thread 4;

RMAN> crosscheck backupset;
Delete expired backupset;

7. alter database open resetlogs;

For more information about Oracle, see the Oracle topic page? Tid = 12