• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

RMAN: SET NEWNAME Command Using SQL

By admin

When you must restore the database the same directory structure is not always available. 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 modified and executed as an RMAN script to complete the task.

Oracle 11g New Command – ‘SET NEWNAME FOR DATABASE’

Starting from 11.2, we can use SET NEWNAME FOR DATABASE clause to avoid using SET NEWNAME for individual datafiles. eg:

SQL> SET NEWNAME FOR DATABASE TO '/oradata1/%b';

Examples

Example 1

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;

Example 2

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 can 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/V102REP1/system01.dbf'; 
set newname for datafile 2 to '/u01/64bit/app/oracle/oradata/V102REP1/undotbs01.dbf'; 
set newname for datafile 3 to '/u01/64bit/app/oracle/oradata/V102REP1/sysaux01.dbf'; 
set newname for datafile 4 to '/u01/64bit/app/oracle/oradata/V102REP1/users01.dbf'; 
set newname for datafile 5 to '/u01/64bit/app/oracle/oradata/V102REP1/fujitsu_1.dbf';

Command to change the above path:

:%s/\/dir1\/dir2/\/newdir1\/newdir2/g
:%s/\/u01\/64bit\/app\/oracle\/oradata\/V102REP1/\/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.

Filed Under: oracle, Oracle 10g, Oracle 11g, RMAN

Some more articles you might also be interested in …

  1. Oracle SQL Script to Detect Tablespace Fragmentation
  2. Oracle RAC Interview Questions – Coherence and Split-Brain
  3. Oracle RMAN Pluggable Database Point in Time Recovery
  4. Oracle Database : Performing Incomplete Recovery from a missing archivelog file (Change-Based, Cancel-Based, Time-Based)
  5. Oracle Database Basics – User Accounts
  6. How to Create Interval-Reference Partitioned Tables in Oracle 12c
  7. Oracle RMAN: Monitoring Recovery Manager Jobs
  8. RMAN Restore/Duplicate Performs Implicit Crosschecking and Cataloging
  9. How to Switch to a New Undo Tablespace in Oracle Database
  10. Script/Queries to Monitor Temporary (TEMP) Tablespace Usage in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary