Check the existing location of all datafiles , redo and control files:
SQL> select name from v$database;
NAME
---------
DBORA
SQL> Archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch/
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/users01.dbf
/u01/app/oracle/oradata/dbora/undotbs01.dbf
/u01/app/oracle/oradata/dbora/sysaux01.dbf
/u01/app/oracle/oradata/dbora/system01.dbf
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/temp01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/redo03.log
/u01/app/oracle/oradata/dbora/redo02.log
/u01/app/oracle/oradata/dbora/redo01.log
SQL> show parameter spfile ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2/d
b_1/dbs/spfiledbora.ora
SQL> show parameter control_file ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/dbora/
control01.ctl, /u01/app/oracle
/fast_recovery_area/dbora/cont
rol02.ctl
Backup pfile and spfile:
SQL> create pfile='/home/oracle/pfile.ora' from spfile ;
File created.
[oracle@oracle arch]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
hc_dbora.dat initdbora.ora init.ora lkDBORA orapwdbora spfiledbora.ora
[oracle@oracle dbs]$ cp spfiledbora.ora /home/oracle/spfiledbora.ora_bkp
Back up the datafiles to the ASM disk group.
The format clause specifies +DATA2, which is the name of the ASM disk group to be used for storing the database.
[oracle@oracle dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 29 17:38:21 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBORA (DBID=3262226103)
RMAN> RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
BACKUP AS COPY
INCREMENTAL LEVEL 0
DATABASE
FORMAT '+DATA2'
TAG 'ORA_ASM_MIGRATION';
}
using target database control file instead of recovery catalog
allocated channel: dev1
channel dev1: SID=48 device type=DISK
allocated channel: dev2
channel dev2: SID=53 device type=DISK
Starting backup at 29-DEC-14
channel dev1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/dbora/system01.dbf
channel dev2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/dbora/sysaux01.dbf
output file name=+DATA2/dbora/datafile/sysaux.257.867606039 tag=ORA_ASM_MIGRATION RECID=1 STAMP=867606314
channel dev2: datafile copy complete, elapsed time: 00:05:38
channel dev2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/dbora/undotbs01.dbf
output file name=+DATA2/dbora/datafile/undotbs1.258.867606335 tag=ORA_ASM_MIGRATION RECID=2 STAMP=867606350
channel dev2: datafile copy complete, elapsed time: 00:00:25
channel dev2: starting datafile copy
copying current control file
output file name=+DATA2/dbora/datafile/system.256.867606009 tag=ORA_ASM_MIGRATION RECID=3 STAMP=867606376
channel dev1: datafile copy complete, elapsed time: 00:06:32
channel dev1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/dbora/users01.dbf
output file name=+DATA2/dbora/controlfile/backup.259.867606353 tag=ORA_ASM_MIGRATION RECID=4 STAMP=867606379
channel dev2: datafile copy complete, elapsed time: 00:00:29
channel dev2: starting incremental level 0 datafile backup set
channel dev2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel dev2: starting piece 1 at 29-DEC-14
output file name=+DATA2/dbora/datafile/users.260.867606381 tag=ORA_ASM_MIGRATION RECID=5 STAMP=867606382
channel dev1: datafile copy complete, elapsed time: 00:00:05
channel dev2: finished piece 1 at 29-DEC-14
piece handle=+DATA2/dbora/backupset/2014_12_29/nnsnn0_ora_asm_migration_0.261.867606385 tag=ORA_ASM_MIGRATION comment=NONE
channel dev2: backup set complete, elapsed time: 00:00:07
Finished backup at 29-DEC-14
released channel: dev1
released channel: dev2
If block change tracking is enabled for the database, then optionally make a level 1 incremental backup that you can use later to recover the database copy.
SQL> select status from v$block_change_tracking ;
STATUS
----------
DISABLED
In our case it was disabled . If enable take level1 DB inc backup as below:
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
BACKUP INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'ORA_ASM_MIGRATION'
DATABASE;
}
If the database is in ARCHIVELOG mode, and if the database is open, then archive the online logs.
The following example uses the SQL command to archive the current redo logs:
RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
If the database instance is currently using a server parameter file, then back it up.
The following example backs up the server parameter file:
RMAN> BACKUP AS BACKUPSET SPFILE;
Starting backup at 29-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-DEC-14
channel ORA_DISK_1: finished piece 1 at 29-DEC-14
piece handle=/u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp tag=TAG20141229T175459 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 29-DEC-14
If block change tracking is enabled, then disable it.
The following command disables block change tracking:
RMAN> SQL "ALTER DATABASE DISABLE BLOCK CHANGE TRACKING";
RMAN> SQL "ALTER DATABASE FLASHBACK OFF";
sql statement: ALTER DATABASE FLASHBACK OFF
RMAN> SHUTDOWN IMMEDIATE;
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP MOUNT;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 536874328 bytes
Database Buffers 297795584 bytes
Redo Buffers 2379776 bytes
RMAN> RESTORE SPFILE TO '+DATA2/spfiledbora.ora';
Starting restore at 29-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA2/spfiledbora.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp tag=TAG20141229T175459
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 29-DEC-14
If the database is not using a server parameter file, then create one in ASM. Execute the CREATE SPFILE command in SQL*Plus as follows, where sid is the SID of the database (the command spans two lines):
SQL> CREATE SPFILE='+DATA/spfiledbora.ora' FROM PFILE='?/dbs/initsid.ora';
Afterward, delete spfiledbora.ora and initdbora.ora from the ?/dbs directory and
Create a new initdbora.ora with the following line of content:
SPFILE='+DATA2/spfiledbora.ora'
RMAN> STARTUP MOUNT;
RMAN> SHUTDOWN IMMEDIATE;
database dismounted
Oracle instance shut down
SQL> STARTUP FORCE NOMOUNT;
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 536874328 bytes
Database Buffers 297795584 bytes
Redo Buffers 2379776 bytes
SQL> alter system set control_files='+DATA','+DATA2' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DATA2' scope=spfile;
System altered.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 536874328 bytes
Database Buffers 297795584 bytes
Redo Buffers 2379776 bytes
SQL> show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA, +DATA2
SQL>
RMAN> restore controlfile from '/u01/app/oracle/oradata/dbora/control01.ctl' ;
Starting restore at 29-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/dbora/controlfile/current.256.867608667
output file name=+DATA2/dbora/controlfile/current.263.867608679
Finished restore at 29-DEC-14
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA2/dbora/datafile/system.256.867606009"
datafile 2 switched to datafile copy "+DATA2/dbora/datafile/sysaux.257.867606039"
datafile 3 switched to datafile copy "+DATA2/dbora/datafile/undotbs1.258.867606335"
datafile 4 switched to datafile copy "+DATA2/dbora/datafile/users.260.867606381"
RMAN> RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
RECOVER DATABASE;
}
allocated channel: dev1
channel dev1: SID=24 device type=DISK
allocated channel: dev2
channel dev2: SID=26 device type=DISK
allocated channel: dev3
channel dev3: SID=27 device type=DISK
allocated channel: dev4
channel dev4: SID=28 device type=DISK
Starting recover at 29-DEC-14
starting media recovery
media recovery complete, elapsed time: 00:01:05
Finished recover at 29-DEC-14
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
RMAN> ALTER DATABASE OPEN;
database opened
QL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA2/dbora/datafile/users.260.867606381
+DATA2/dbora/datafile/undotbs1.258.867606335
+DATA2/dbora/datafile/sysaux.257.867606039
+DATA2/dbora/datafile/system.256.867606009
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/temp01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/redo03.log
/u01/app/oracle/oradata/dbora/redo02.log
/u01/app/oracle/oradata/dbora/redo01.log
SQL> show parameter spfile ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2/d
b_1/dbs/spfiledbora.ora
SQL> show parameter control_file ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/dbora/controlfile/curren
t.256.867608667, +DATA2/dbora/
controlfile/current.263.867608
679
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ;
Tablespace altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA2/dbora/tempfile/temp.264.867609077
SQL> SET SERVEROUTPUT ON;
DECLARE
CURSOR rlc IS
SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
FROM V$LOG
UNION
SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
FROM V$STANDBY_LOG
ORDER BY 1;
stmt VARCHAR2(2048);
BEGIN
FOR rlcRec IN rlc LOOP
IF (rlcRec.srl = 'YES') THEN
stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
EXECUTE IMMEDIATE stmt;
ELSE
stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
BEGIN
stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE stmt;
END;
END IF;
END LOOP;
END;
/SQL>
ALTER DATABASE DROP LOGFILE GROUP 1
ALTER DATABASE DROP LOGFILE GROUP 2
ALTER DATABASE DROP LOGFILE GROUP 3
PL/SQL procedure successfully completed.
SQL> SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA2/dbora/onlinelog/group_2.269.867609239
+DATA2/dbora/onlinelog/group_1.267.867609167
+DATA2/dbora/onlinelog/group_4.265.867609139
+DATA2/dbora/onlinelog/group_4.266.867609151
+DATA2/dbora/onlinelog/group_1.268.867609179
+DATA2/dbora/onlinelog/group_2.270.867609251
Back up archived redo log files, backup sets, and data file copies to Oracle ASM. For example, run the following command at the RMAN prompt:
RMAN> RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
BACKUP BACKUPSET ALL DELETE INPUT;
BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;
}
using target database control file instead of recovery catalog
allocated channel: dev1
channel dev1: SID=58 device type=DISK
allocated channel: dev2
channel dev2: SID=59 device type=DISK
allocated channel: dev3
channel dev3: SID=60 device type=DISK
allocated channel: dev4
channel dev4: SID=61 device type=DISK
Starting backup at 29-DEC-14
current log archived
channel dev1: starting archived log copy
input archived log thread=1 sequence=3 RECID=1 STAMP=867606655
channel dev2: starting archived log copy
input archived log thread=1 sequence=4 RECID=2 STAMP=867606867
channel dev3: starting archived log copy
input archived log thread=1 sequence=5 RECID=3 STAMP=867609204
channel dev4: starting archived log copy
input archived log thread=1 sequence=6 RECID=4 STAMP=867609456
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_4.272.867609463 RECID=5 STAMP=867609472
channel dev2: archived log copy complete, elapsed time: 00:00:21
channel dev2: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_4_867594243.arc RECID=2 STAMP=867606867
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_5.273.867609467 RECID=6 STAMP=867609476
channel dev3: archived log copy complete, elapsed time: 00:00:20
channel dev3: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_5_867594243.arc RECID=3 STAMP=867609204
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_6.274.867609471 RECID=7 STAMP=867609479
channel dev4: archived log copy complete, elapsed time: 00:00:18
channel dev4: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_6_867594243.arc RECID=4 STAMP=867609456
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_3.271.867609459 RECID=8 STAMP=867609488
channel dev1: archived log copy complete, elapsed time: 00:00:37
channel dev1: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_3_867594243.arc RECID=1 STAMP=867606655
Finished backup at 29-DEC-14
Starting backup at 29-DEC-14
channel dev1: input backup set: count=6, stamp=867606384, piece=1
channel dev1: starting piece 1 at 29-DEC-14
channel dev1: backup piece +DATA2/dbora/backupset/2014_12_29/nnsnn0_ora_asm_migration_0.261.867606385
channel dev2: input backup set: count=7, stamp=867606900, piece=1
channel dev2: starting piece 1 at 29-DEC-14
channel dev2: backup piece /u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp
piece handle=+DATA2/dbora/backupset/2014_12_29/nnnnf0_0.276.867609507 comment=NONE
channel dev1: finished piece 1 at 29-DEC-14
channel dev1: backup piece complete, elapsed time: 00:00:07
deleted backup piece
backup piece handle=+DATA2/dbora/backupset/2014_12_29/nnsnn0_ora_asm_migration_0.261.867606385 RECID=1 STAMP=867606384
piece handle=+DATA2/dbora/backupset/2014_12_29/nnnnf0_0.275.867609507 comment=NONE
channel dev2: finished piece 1 at 29-DEC-14
channel dev2: backup piece complete, elapsed time: 00:00:09
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp RECID=2 STAMP=867606900
Finished backup at 29-DEC-14
Starting backup at 29-DEC-14
channel dev1: starting datafile copy
input is copy of datafile 00001: /u01/app/oracle/oradata/dbora/system01.dbf
channel dev2: starting datafile copy
input is copy of datafile 00002: /u01/app/oracle/oradata/dbora/sysaux01.dbf
channel dev3: starting datafile copy
input is copy of datafile 00003: /u01/app/oracle/oradata/dbora/undotbs01.dbf
channel dev4: starting datafile copy
input is copy of datafile 00004: /u01/app/oracle/oradata/dbora/users01.dbf
output file name=+DATA2/dbora/datafile/users.279.867609541 RECID=10 STAMP=867609562
channel dev4: datafile copy complete, elapsed time: 00:00:40
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/users01.dbf RECID=9 STAMP=867608773
output file name=+DATA2/dbora/datafile/undotbs1.278.867609533 RECID=11 STAMP=867609567
channel dev3: datafile copy complete, elapsed time: 00:00:54
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/undotbs01.dbf RECID=8 STAMP=867608771
output file name=+DATA2/dbora/datafile/sysaux.277.867609525 RECID=12 STAMP=867609831
channel dev2: datafile copy complete, elapsed time: 00:05:13
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/sysaux01.dbf RECID=7 STAMP=867608769
output file name=+DATA2/dbora/datafile/system.261.867609525 RECID=13 STAMP=867609859
channel dev1: datafile copy complete, elapsed time: 00:05:37
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/system01.dbf RECID=6 STAMP=867608768
Finished backup at 29-DEC-14
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4
RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBORA
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM *** +DATA2/dbora/datafile/system.256.867606009
2 550 SYSAUX *** +DATA2/dbora/datafile/sysaux.257.867606039
3 35 UNDOTBS1 *** +DATA2/dbora/datafile/undotbs1.258.867606335
4 5 USERS *** +DATA2/dbora/datafile/users.260.867606381
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 100 TEMP 32767 +DATA2/dbora/tempfile/temp.264.867609077
