GOAL
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
When you must restore the database the same directory structure is not always there. If you have a database containing 1000s of datafiles it can be very tedious to setup the set newname commands for all the datafiles. Using sqlplus we can extract the information we need into a file which can then be easily modifed and executed as an RMAN script to complete the task.
Starting from 11.2, we can use SET NEWNAME FOR DATABASE clause to avoid using SET NEWNAME for individual datafiles. eg:
SET NEWNAME FOR DATABASE TO '/oradata1/%b';
SOLUTION
You are restoring or duplicating the target database to a new host using RMAN. The datafiles are not OMF files and you want to make them OMF. Using 'set newname for datafile to NEW' will generate a new OMF filename for the restored datafile. This will avoid the manual entry or vi/notepad editing of similar output. Using this output the datafiles will be restored to the DB_CREATE_FILE_DEST. If this parameter is not set you must add the correct path as in '/path/NEW' will direct the files to the new location and give an OMF filename.
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile '||file#||' to NEW;' from v$datafile;
-- select 'set newname for datafile '||file#||' to /newpath/NEW;' from v$datafile;
spool off
There are 2 select statements above with slightly different output.
Select #1 Output:
set newname for datafile 1 to NEW;
set newname for datafile 2 to NEW;
set newname for datafile 3 to NEW;
set newname for datafile 4 to NEW;
set newname for datafile 5 to NEW;
Select #2 Output:
set newname for datafile 1 to /newpath/NEW;
set newname for datafile 2 to /newpath/NEW;
set newname for datafile 3 to /newpath/NEW;
set newname for datafile 4 to /newpath/NEW;
set newname for datafile 5 to /newpath/NEW;
In this scenario you do not use OMF naming for your files and you want to continue to control the datafile names. To generate set newname commands to point to an ASM volume execute the sql below. It will create a file that you just add your restore command to complete the script and execute in RMAN inside a run block.
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile '||file#||' to ''+DG'';' from v$datafile;
spool off
Select #3 Output:
set newname for datafile 1 to '+DG';
set newname for datafile 2 to '+DG';
set newname for datafile 3 to '+DG';
set newname for datafile 4 to '+DG';
set newname for datafile 5 to '+DG';
With the following query you keep the same path and name as on the original target. Using vi global search and replace you change change the path to the new directory using %s. This becomes very useful when there are 1000s of files to update. If using multiple directories you can split the output to change the path all in a file then merge the files or find a quicker method. Here is the example output of before and after the search and replace.
set echo off pages 0 feed off sqlp #
spool setnewnamedf.lst
select 'set newname for datafile '||file#||' to '''||name||''';' from v$datafile;
spool off
Select #4 Output: Before change:
set newname for datafile 1 to '/u01/64bit/app/oracle/oradata/ORCL/system01.dbf';
set newname for datafile 2 to '/u01/64bit/app/oracle/oradata/ORCL/undotbs01.dbf';
set newname for datafile 3 to '/u01/64bit/app/oracle/oradata/ORCL/sysaux01.dbf';
set newname for datafile 4 to '/u01/64bit/app/oracle/oradata/ORCL/users01.dbf';
set newname for datafile 5 to '/u01/64bit/app/oracle/oradata/ORCL/fujitsu_1.dbf';
Command to change the above path:
:%s/\/dir1\/dir2/\/newdir1\/newdir2/g
:%s/\/u01\/64bit\/app\/oracle\/oradata\/ORCL/\/newpath/g
After executing the search and replace we are left with:
set newname for datafile 1 to '/newpath/system01.dbf';
set newname for datafile 2 to '/newpath/undotbs01.dbf';
set newname for datafile 3 to '/newpath/sysaux01.dbf';
set newname for datafile 4 to '/newpath/users01.dbf';
set newname for datafile 5 to '/newpath/fujitsu_1.dbf';
Using these methods, or something similar with other editors, you can quickly setup a restore or duplicate script to be used on a new host and directory structure no matter the number of files.
------12.2
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 19.4.0.0.0 [Release 12.1 to 19]
Information in this document applies to any platform.
GOAL
Goal of this Document it to explain how to use setnewname for database in CDB envirnoment when we are doing a restore to same Mount point or disk group
SOLUTION
In this Document
NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data
from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material.
Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.
In this Example we are assuming :-
Db name : ORCL
Destination server Diskgroup path :- +DATA/ORCL
Source database had datafiles in Multiple Diskgroup and is CDB with Multiple PDB (PDB names are : PDB1 and PDB2)
On Destination the restore is going to take place under
Single Location --->+DATA/ORCL
Multiple Location :-+DATA/ORCL , +DATA/ORCL/PDB1/DATAFILE ,+DATA/ORCL/PDB2/DATAFILE ,+DATA/ORCL/pdbseed/DATAFILE
You can use
SET NEWNAME FOR DATABASE to '<destination>/<format>
Set newname for database has Multiple Format
%U as it gives unique name. However it has a restriction and name generated cannot exceed 30 character .So if datafile name is already lengthy this would fail .
%b Specifies the filename without the fully qualified directory path. For example, the datafile name +DATAC!/<Source path>/orcl.dbf is transformed to orcl.dbf .
%f Specifies the absolute file number of the datafile for which the new name is generated. For example, if datafile 2 is duplicated, then %f generates the value 2.
Replace SET NEWNAME FOR DATABASE as per your requirement
If using %U -- SET NEWNAME FOR DATABASE to '<PATH>/%U'
If using combination of %f_%b -- SET NEWNAME FOR DATABASE to '<PATH>/%f_%b'
%b alone is not recommended because if the datafiles from multiple location present in source with same name are restore to same location on destination the files with same name would be overwritten
For NON CDB
run {
set newname for database to '+DATA/ORCL/DATAFILE/%f_%b';
restore database;
Switch datafile all ; }
Or
run {
set newname for database to '+DATA/ORCL/DATAFILE/%U'; --------------------> has restriction on the length of the filename and might error out if file name is lengthy
restore database;
Switch datafile all ; }
For CDB
If restoring into same directory structure (+DATA/ORCL/DATAFILE)
run {
set newname for database root to '+DATA/ORCL/DATAFILE/%f_%b';
set newname for database "PDB$SEED" to to '+DATA/ORCL//DATAFILE/%f_%b';
set newname for pluggable database PDB1 to '+DATA/ORCL/DATAFILE/%f_%b';
set newname for pluggable database PDB1 to '+DATA/ORCL/DATAFILE/%f_%b';
restore database;
Switch datafile all ; }
or
run {
set newname for database to '+DATA/ORCL/DATAFILE/%f_%b';
restore database;
Switch datafile all ; }
When doing restore to Multiple location 一个pdb一个名字啦
run {
set newname for database root to '+DATA/ORCL/%f_%b';
set newname for database "PDB$SEED" to '+DATA/ORCL/pdbseed/DATAFILE/%f_%b';
set newname for pluggable database PDB1 to '+DATA/ORCL/PDB1/DATAFILE/%f_%b';
set newname for pluggable database PDB1 to '+DATA/ORCL/PDB2/DATAFILE/%f_%b';
restore database;
Switch datafile all ; }
Common error :-
1> When using % U ---> ORA-15126: component within ASM file name '<name>' exceeds maximum length
2> When using %b --> If source datafiles in different directory have same name and if restore is done in single directory then files might get overwritten on destination due to same filename.
Example :- If source file 3 has path <PATH1>/<directory1>/orcl.dbf and file 5 has <PATH2>/<directory2>/orcl.dbf ---> On Destination restore done to only <PATH1> . Then file 3 after getting
restored will get overwritten by file 5 which has same name.
3>restore of PDBSEED datafile not happening to new directory :
This could happen because of syntax issue
set newname for pluggable database pdbseed to ( $ sign missing and not given in Double quotes)
************
Replace With
set newname for pluggable database "PDB$SEED" to
or
set newname for database "PDB$SEED" to
------------------root pluggable database 有时候不用---------------------
APPLIES TO:
Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.
SYMPTOMS
When restoring a PDB tablespace and using "set newname for database", RMAN tries to restore the Root datafiles to the source database directory. I.e., ignoring the 'set newname for database'. If permission of that source directory is not present, or the directory does not exists, then error will occur.
For example, at source DB, data file is under the following directory:
<PATH_1>
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name <pdb name>
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM YES <PATH_1>/system01.dbf
3 750 SYSAUX NO <PATH_1>/sysaux01.dbf
4 295 UNDOTBS1 YES <PATH_1>/undotbs01.dbf
5 270 PDB$SEED:SYSTEM NO <PATH_1>/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX NO <PATH_1>/pdbseed/sysaux01.dbf
7 5 USERS NO <PATH_1>/users01.dbf
8 100 PDB$SEED:UNDOTBS1 NO <PATH_1>/pdbseed/undotbs01.dbf
9 270 <PDBNAME>:SYSTEM NO <PATH_1>/<PDBNAME>/system01.dbf
10 340 <PDBNAME>:SYSAUX NO <PATH_1>/<PDBNAME>/sysaux01.dbf
11 100 <PDBNAME>:UNDOTBS1 NO <PATH_1>/<PDBNAME>/undotbs01.dbf
12 5 <PDBNAME>:USERS NO <PATH_1>/<PDBNAME>/users01.dbf
13 100 <PDBNAME>:TBS001 NO <PATH_1>/tbs001.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 132 TEMP 32767 <PATH_1>/temp01.dbf
2 36 PDB$SEED:TEMP 32767 <PATH_1>/pdbseed/temp012020-10-17_02-57-57-800-AM.dbf
3 36 <PDBNAME>:TEMP 32767 <PATH_1>/<PDBNAME>/temp01.dbf
RMAN>
We want to restore to the following directory：
<PATH_2>
run{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
SET UNTIL TIME = "to_date('2021-09-09 23:49:18','YYYY/MM/DD HH24:MI:SS')";
set newname for database root to '<PATH_2>%U';
set newname for database "PDB$SEED" to '<PATH_2>%U';
set newname for tablespace <PDBNAME>:SYSTEM to '<PATH_2>%U';
set newname for tablespace <PDBNAME>:SYSAUX to '<PATH_2>%U';
set newname for tablespace <PDBNAME>:UNDOTBS1 to '<PATH_2>%U';
set newname for tablespace <PDBNAME>:TBS001 to '<PATH_2>%U';
restore database root skip tablespace users database "PDB$SEED" database <PDBNAME> skip tablespace <PDBNAME>:USERS;
switch datafile all;
}
......
Starting restore at 17-SEP-21
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to <PATH_1>/system01.dbf
channel t1: restoring datafile 00003 to <PATH_1>/sysaux01.dbf
channel t1: restoring datafile 00004 to <PATH_1>/undotbs01.dbf
channel t1: reading from backup piece <BACKUP LOCATION>/db_0108lv6k_1_1
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00009 to <PATH_2>data_D-<CDBNAME>_TS-SYSTEM_FNO-9
channel t2: restoring datafile 00010 to <PATH_2>data_D-<CDBNAME>_TS-SYSAUX_FNO-10
channel t2: restoring datafile 00011 to <PATH_2>data_D-<CDBNAME_TS-UNDOTBS1_FNO-11
channel t2: restoring datafile 00013 to <PATH_2>data_D-<CDBNAME>_TS-TBS001_FNO-13
channel t2: reading from backup piece <BACKUP LOCATION>/db_0208lv73_1_1
channel t3: starting datafile backup set restore
channel t3: specifying datafile(s) to restore from backup set
channel t3: restoring datafile 00005 to <PATH_2>data_D-<CDBNAME>_TS-SYSTEM_FNO-5
channel t3: restoring datafile 00006 to <PATH_2>data_D-<CDBNAME>_TS-SYSAUX_FNO-6
channel t3: restoring datafile 00008 to <PATH_2>data_D-<CDBNAME>_TS-UNDOTBS1_FNO-8
channel t3: reading from backup piece <BACKUP LOCATION>/db_0308lv7a_1_1
channel t1: ORA-19870: error while restoring backup piece <BACKUP LOCATION>/db_0108lv6k_1_1
ORA-19504: failed to create file "<PATH_1>/system01.dbf" <<<<<<< tried to restore root database file to source DB location
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
channel t3: piece handle=<BACKUP LOCATION>/db_0308lv7a_1_1 tag=TAG20210909T234836
channel t3: restored backup piece 1
channel t3: restore complete, elapsed time: 00:00:07
channel t2: piece handle=<BACKUP LOCATION>/db_0208lv73_1_1 tag=TAG20210909T234836
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:15
failover to previous backup
released channel: t1
released channel: t2
released channel: t3
released channel: t4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/17/2021 01:02:00
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
CHANGES
CAUSE
The issue was investigated via bug 33388218, closed as "not a bug"， this is a usage issue.
SOLUTION
The command being used was:
set newname for database root to '<PATH_2>/%U';
It should be:
set newname for database cdb$root to '<PATH_2>/%U';
This can succeed without error.
995
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
