======Overview======
The migration from ASM <-> FS is usually easy process if you know how to do it :), what is important to be moved are:
Mandatory files:
* Datafiles
* Redolog files
* Controlfiles
* Spfile/Pfile
Optional Files (but good to migrate as well):
* Backups
* Archivelogs
Although, the optional files you can always migrate, without restarting/shutting down the server.
======Datafiles======
Datafiles migration can be done very easily using the "Backup as copy" feature of RMAN as follows:
P.S. That can be done both in Open(Assuming you are in Archivelog mode) and Mount state
[oracle@Servername datafiles]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jul 16 13:33:05 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: RCAT (DBID=DBIDNUM, not open)
RMAN> RUN
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/app/data/RCAT/datafiles/%U';
BACKUP AS COPY DATABASE;
release channel disk1;
} 2> 3> 4> 5> 6>
released channel: ORA_DISK_1
allocated channel: disk1
channel disk1: SID=18 device type=DISK
Starting backup at 16-JUL-18
channel disk1: starting datafile copy
input datafile file number=00005 name=+RCATDATA/RCAT/DATAFILE/rman_data.266.952702327
output file name=/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-RMAN_DATA_FNO-5_7jt8581f tag=TAG20180716T133335 RECID=7 STAMP=981639222
channel disk1: datafile copy complete, elapsed time: 00:00:15
channel disk1: starting datafile copy
input datafile file number=00003 name=+RCATDATA/RCAT/DATAFILE/sysaux.257.952360417
output file name=/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-3_7kt8581u tag=TAG20180716T133335 RECID=8 STAMP=981639238
channel disk1: datafile copy complete, elapsed time: 00:00:15
channel disk1: starting datafile copy
input datafile file number=00001 name=+RCATDATA/RCAT/DATAFILE/system.256.952360373
output file name=/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSTEM_FNO-1_7lt8582d tag=TAG20180716T133335 RECID=9 STAMP=981639252
channel disk1: datafile copy complete, elapsed time: 00:00:07
channel disk1: starting datafile copy
input datafile file number=00002 name=/app/data/rcat/datafiles/sysaux_dbf02.dbf
output file name=/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-2_7mt8582k tag=TAG20180716T133335 RECID=10 STAMP=981639254
channel disk1: datafile copy complete, elapsed time: 00:00:03
channel disk1: starting datafile copy
input datafile file number=00004 name=+RCATDATA/RCAT/DATAFILE/undotbs1.258.952360443
output file name=/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-UNDOTBS1_FNO-4_7nt8582n tag=TAG20180716T133335 RECID=11 STAMP=981639256
channel disk1: datafile copy complete, elapsed time: 00:00:01
channel disk1: starting datafile copy
input datafile file number=00007 name=+RCATDATA/RCAT/DATAFILE/users.259.952360445
output file name=/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-USERS_FNO-7_7ot8582p tag=TAG20180716T133335 RECID=12 STAMP=981639257
channel disk1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-JUL-18
Starting Control File and SPFILE Autobackup at 16-JUL-18
piece handle=+RCATFRA/RCAT/AUTOBACKUP/2018_07_16/s_981639073.262.981639259 comment=NONE
Finished Control File and SPFILE Autobackup at 16-JUL-18
released channel: disk1
Once the backup of the database files has been done, we can switch to copy (that should be done in Mount)
RMAN> switch database to copy;
datafile 1 switched to datafile copy "/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSTEM_FNO-1_7lt8582d"
datafile 2 switched to datafile copy "/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-2_7mt8582k"
datafile 3 switched to datafile copy "/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-3_7kt8581u"
datafile 4 switched to datafile copy "/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-UNDOTBS1_FNO-4_7nt8582n"
datafile 5 switched to datafile copy "/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-RMAN_DATA_FNO-5_7jt8581f"
datafile 7 switched to datafile copy "/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-USERS_FNO-7_7ot8582p"
RMAN>
Recovery Manager complete.
Once we are switched, we can startup our DB.
P.S. If you have done the backup in Open mode you have to recover the copy.
[oracle@servername datafiles]$
[oracle@servername datafiles]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 13:34:37 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> SET LINES 132
COL name FORM a60
COL status FORM A8
COL file# FORM 9999
COL control_file_SCN FORM 999999999999999
COL datafile_SCN FORM 999999999999999
SELECT
a.name
,a.status
,a.file#
,a.checkpoint_change# control_file_SCN
,b.checkpoint_change# datafile_SCN
,CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((b.checkpoint_change#) = 0) THEN 'File Missing?'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File' ELSE 'what the ?'
END datafile_status
FROM v$datafile a -- control file SCN for datafile
,v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#
ORDER BY a.file#;SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
NAME STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS
------------------------------------------------------------ -------- ----- ---------------- ---------------- ----------------
/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSTEM_F SYSTEM 1 39317158 39317158 Startup Normal
NO-1_7lt8582d
/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_F ONLINE 2 39317158 39317158 Startup Normal
NO-2_7mt8582k
/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_F ONLINE 3 39317158 39317158 Startup Normal
NO-3_7kt8581u
/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-UNDOTBS1 ONLINE 4 39317158 39317158 Startup Normal
_FNO-4_7nt8582n
NAME STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS
------------------------------------------------------------ -------- ----- ---------------- ---------------- ----------------
/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-RMAN_DAT ONLINE 5 39317158 39317158 Startup Normal
A_FNO-5_7jt8581f
/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-USERS_FN ONLINE 7 39317158 39317158 Startup Normal
O-7_7ot8582p
6 rows selected.
SQL> alter database open;
Database altered.
SQL> alter tablespace TEMP add tempfile '/app/data/RCAT/datafiles/temp01.dbf' size 100M;
Tablespace altered.
SQL>
After that, we can continue with the redo log files :)
======Redo log files======
Unlike the database files, the relocation of the redolog files, we can do completely online :) as follows:
First it is important to get two things:
* The sizes of the groups
* The current/active/inactive groups
Then it is just adding new group and dropping a group, remember we can drop only INACTIVE groups :)
SQL> /
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 6556 209715200 512 2 YES INACTIVE 39311679 16-JUL-18 39314368 16-JUL-18 0
2 1 6557 209715200 512 2 NO CURRENT 39314368 16-JUL-18 1.8447E+19 0
3 1 6555 209715200 512 2 YES INACTIVE 39309094 16-JUL-18 39311679 16-JUL-18 0
SQL> select bytes/1024/1024 from v$log;
BYTES/1024/1024
---------------
200
200
200
SQL> alter database add logfile group 4 ('/app/data/RCAT/archivelogs/log4a.rdo','/app/data/RCAT/archivelogs/log4b') size 200M;^C
SQL>
SQL> alter database add logfile group 4 ('/app/data/RCAT/redologs/log4a.rdo','/app/data/RCAT/redologs/log4b') size 200M;
alter database add logfile group 5 ('/app/data/RCAT/redologs/log5a.rdo','/app/data/RCAT/redologs/log5b') size 200M;
alter database add logfile group 6 ('/app/data/RCAT/redologs/log6a.rdo','/app/data/RCAT/redologs/log6b') size 200M;
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 6556 209715200 512 2 YES INACTIVE 39311679 16-JUL-18 39314368 16-JUL-18 0
2 1 6557 209715200 512 2 NO CURRENT 39314368 16-JUL-18 1.8447E+19 0
3 1 6555 209715200 512 2 YES INACTIVE 39309094 16-JUL-18 39311679 16-JUL-18 0
4 1 0 209715200 512 2 YES UNUSED 0 0 0
5 1 0 209715200 512 2 YES UNUSED 0 0 0
6 1 0 209715200 512 2 YES UNUSED 0 0 0
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 6556 209715200 512 2 YES INACTIVE 39311679 16-JUL-18 39314368 16-JUL-18 0
2 1 6557 209715200 512 2 YES ACTIVE 39314368 16-JUL-18 39318488 16-JUL-18 0
3 1 6555 209715200 512 2 YES INACTIVE 39309094 16-JUL-18 39311679 16-JUL-18 0
4 1 6558 209715200 512 2 YES ACTIVE 39318488 16-JUL-18 39318491 16-JUL-18 0
5 1 6559 209715200 512 2 NO CURRENT 39318491 16-JUL-18 1.8447E+19 0
6 1 0 209715200 512 2 YES UNUSED 0 0 0
6 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
2 1 6557 209715200 512 2 YES ACTIVE 39314368 16-JUL-18 39318488 16-JUL-18 0
3 1 6555 209715200 512 2 YES INACTIVE 39309094 16-JUL-18 39311679 16-JUL-18 0
4 1 6558 209715200 512 2 YES ACTIVE 39318488 16-JUL-18 39318491 16-JUL-18 0
5 1 6559 209715200 512 2 NO CURRENT 39318491 16-JUL-18 1.8447E+19 0
6 1 0 209715200 512 2 YES UNUSED 0 0 0
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
2 1 6557 209715200 512 2 YES ACTIVE 39314368 16-JUL-18 39318488 16-JUL-18 0
4 1 6558 209715200 512 2 YES ACTIVE 39318488 16-JUL-18 39318491 16-JUL-18 0
5 1 6559 209715200 512 2 YES ACTIVE 39318491 16-JUL-18 39318536 16-JUL-18 0
6 1 6560 209715200 512 2 NO CURRENT 39318536 16-JUL-18 1.8447E+19 0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
2 1 6561 209715200 512 2 NO CURRENT 39318543 16-JUL-18 1.8447E+19 0
4 1 6558 209715200 512 2 YES INACTIVE 39318488 16-JUL-18 39318491 16-JUL-18 0
5 1 6559 209715200 512 2 YES ACTIVE 39318491 16-JUL-18 39318536 16-JUL-18 0
6 1 6560 209715200 512 2 YES ACTIVE 39318536 16-JUL-18 39318543 16-JUL-18 0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
2 1 6561 209715200 512 2 YES ACTIVE 39318543 16-JUL-18 39318550 16-JUL-18 0
4 1 6562 209715200 512 2 NO CURRENT 39318550 16-JUL-18 1.8447E+19 0
5 1 6559 209715200 512 2 YES ACTIVE 39318491 16-JUL-18 39318536 16-JUL-18 0
6 1 6560 209715200 512 2 YES ACTIVE 39318536 16-JUL-18 39318543 16-JUL-18 0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
2 1 6561 209715200 512 2 YES INACTIVE 39318543 16-JUL-18 39318550 16-JUL-18 0
4 1 6562 209715200 512 2 YES INACTIVE 39318550 16-JUL-18 39318554 16-JUL-18 0
5 1 6563 209715200 512 2 NO CURRENT 39318554 16-JUL-18 1.8447E+19 0
6 1 6560 209715200 512 2 YES INACTIVE 39318536 16-JUL-18 39318543 16-JUL-18 0
SQL> alter database drop logfile group 2;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
4 1 6562 209715200 512 2 YES INACTIVE 39318550 16-JUL-18 39318554 16-JUL-18 0
5 1 6563 209715200 512 2 NO CURRENT 39318554 16-JUL-18 1.8447E+19 0
6 1 6560 209715200 512 2 YES INACTIVE 39318536 16-JUL-18 39318543 16-JUL-18 0
SQL>
======Controlfiles======
Finally we have to re-create the controlfiles, we need to restart the database in order to do that.
First we have to do a text backup of the controlfile:
[oracle@Naos controlfiles]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 14:40:08 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter database backup controlfile to trace as '/app/data/RCAT/controlfiles/bkpctrl.sh' ;
Database altered.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
We have to edit the controlfile after that, in order to use only the part which we need: Your controlfile should have ONLY the entries which you need.
[oracle@Naos controlfiles]$ cat bkpctrl.sh
CREATE CONTROLFILE REUSE DATABASE "RCAT" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
'/app/data/RCAT/redologs/log4a.rdo',
'/app/data/RCAT/redologs/log4b'
) SIZE 200M BLOCKSIZE 512,
GROUP 5 (
'/app/data/RCAT/redologs/log5a.rdo',
'/app/data/RCAT/redologs/log5b'
) SIZE 200M BLOCKSIZE 512,
GROUP 6 (
'/app/data/RCAT/redologs/log6a.rdo',
'/app/data/RCAT/redologs/log6b'
) SIZE 200M BLOCKSIZE 512
DATAFILE
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSTEM_FNO-1_7lt8582d',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-2_7mt8582k',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-3_7kt8581u',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-UNDOTBS1_FNO-4_7nt8582n',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-RMAN_DATA_FNO-5_7jt8581f',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-USERS_FNO-7_7ot8582p'
CHARACTER SET AL32UTF8
;
After that, we have to modify the pfile, to point to location OUTSIDE of the ASM.
*.control_files='/app/data/RCAT/controlfiles/control01.ctl','/u01/app/oracle/product/12.2.0/rcat_home/dbs/control02.ctl'
After we modify the pfile, start the instance into a mount state and re-create the controlfile as follows:
[oracle@Naos controlfiles]$ cat bkpctrl.sh
CREATE CONTROLFILE REUSE DATABASE "RCAT" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 4 (
'/app/data/RCAT/redologs/log4a.rdo',
'/app/data/RCAT/redologs/log4b'
) SIZE 200M BLOCKSIZE 512,
GROUP 5 (
'/app/data/RCAT/redologs/log5a.rdo',
'/app/data/RCAT/redologs/log5b'
) SIZE 200M BLOCKSIZE 512,
GROUP 6 (
'/app/data/RCAT/redologs/log6a.rdo',
'/app/data/RCAT/redologs/log6b'
) SIZE 200M BLOCKSIZE 512
DATAFILE
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSTEM_FNO-1_7lt8582d',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-2_7mt8582k',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-SYSAUX_FNO-3_7kt8581u',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-UNDOTBS1_FNO-4_7nt8582n',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-RMAN_DATA_FNO-5_7jt8581f',
'/app/data/RCAT/datafiles/data_D-RCAT_I-724510117_TS-USERS_FNO-7_7ot8582p'
CHARACTER SET AL32UTF8
;
[oracle@Naos controlfiles]$ vi bkpctrl.sh
[oracle@Naos controlfiles]$ sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 16 15:09:14 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> !ls -lart
total 12
drwxr-xr-x 6 oracle oinstall 4096 Jul 16 13:07 ..
-rw-r--r-- 1 oracle dba 1042 Jul 16 15:09 bkpctrl.sh
drwxr-xr-x 2 oracle oinstall 4096 Jul 16 15:09 .
SQL> @bkpctrl.sh
Control File created.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Feel free to open the database at this point :)
SQL> alter database open;
Database altered.
SQL>
----
======Appendix=====
You can use the following procedure to generate all commands if you are lazy:
SET serveroutput ON;
DECLARE
vcount NUMBER:=0;
vfname VARCHAR2(1024);
CURSOR df
IS
SELECT file#,
rtrim(REPLACE(name,'+DG_DATA/drop/datafile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
FROM v$datafile;
CURSOR tp
IS
SELECT file#,
rtrim(REPLACE(name,'+DG_DATA/drop/tempfile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
FROM v$tempfile;
BEGIN
dbms_output.put_line('CONFIGURE CONTROLFILE AUTOBACKUP ON;');
FOR dfrec IN df
LOOP
IF dfrec.name != vfname THEN
vcount :=1;
vfname := dfrec.name;
ELSE
vcount := vcount+1;
vfname:= dfrec.name;
END IF;
dbms_output.put_line('backup as copy datafile ' || dfrec.file# ||' format "'||dfrec.name ||vcount||'.dbf";');
END LOOP;
dbms_output.put_line('run');
dbms_output.put_line('{');
FOR dfrec IN df
LOOP
IF dfrec.name != vfname THEN
vcount :=1;
vfname := dfrec.name;
ELSE
vcount := vcount+1;
vfname:= dfrec.name;
END IF;
dbms_output.put_line('set newname for datafile ' || dfrec.file# ||' to '''||dfrec.name ||vcount||'.dbf'' ;');
END LOOP;
FOR tprec IN tp
LOOP
IF tprec.name != vfname THEN
vcount :=1;
vfname := tprec.name;
ELSE
vcount := vcount+1;
vfname:= tprec.name;
END IF;
dbms_output.put_line('set newname for tempfile ' || tprec.file# ||' to '''||tprec.name ||vcount||'.dbf'' ;');
END LOOP;
dbms_output.put_line('switch tempfile all;');
dbms_output.put_line('switch datafile all;');
dbms_output.put_line('restore database;');
dbms_output.put_line('recover database;');
dbms_output.put_line('}');
dbms_output.put_line('alter database open;');
dbms_output.put_line('exit');
END;
/
======Incremental backup recovery of database copy======
As complex as it sound, you can create incrementally updated copies of tha database:
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/app/data/orcltest/datafiles/%U';
RECOVER COPY OF DATABASE WITH TAG 'mydb_incr_backup' UNTIL TIME 'SYSDATE - 7';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'mydb_incr_backup' DATABASE;
}
This script will either do a recover a copy OR create a backup.