Hi all,

At this post, I want to talk about how to restore/recover a RMAN backup that taken from an ASM (on ODA X7-2M) to FILE SYSTEM with different SID, different database files location and different backup location.

My Environments

SidePlatformDatabase VersionOracle SIDDatabase LocationBackup LocationHostname
SourceODA X7-2M12.2.0.1.0ASMDBDATA (DiskGroup)/asmbck/backup_areaasmdb-host
TargetOracle Linux 7.6 x86_6412.2.0.1.0FSDBhome/oracle/oradb/home/oracle/backup_areafsdb-host
My Environments

I. SOURCE SIDE

A. Backup Database

For store backup database you can use default RMAN backup location (+RECO). But I optionally chose creating different ACFS disk volume for backup location.

A.1. Create ACFS Disk Volume

grid > asmca -silent -createVolume -volumeName asmbck -volumeDiskGroup DATA -volumeSizeGB 800 -sysAsmPassword "password"
grid > mkfs -t acfs /dev/asm/asmbck-473
root > mkdir -p /asmbck
root > acfsutil registry -a /dev/asm/asmbck-473 /asmbck

A.2. Create Backup Location

root > mkdir -p /asmbck/backup_area
root > chown -R oracle:oinstall /asmbck/backup_area

A.3. RMAN Backup

You can use the followig script for RMAN backup.

RMAN > 
-
run 
{
ALLOCATE CHANNEL RMAN_BACKUP_CH01 TYPE DISK FORMAT '/asmbck/backup_area/%T_%U.bkp';
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
CROSSCHECK BACKUP;
BACKUP DATABASE INCLUDE CURRENT CONTROLFILE PLUS ARCHIVELOG;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-1';
CROSSCHECK ARCHIVELOG ALL;
RELEASE CHANNEL RMAN_BACKUP_CH01;
}

A.3.a. My RMAN Backup Files

/asmbck/backup_area/20191219_5oujrkfg_1_1.bkp
/asmbck/backup_area/20191219_5pujrkgj_1_1.bkp
/asmbck/backup_area/20191219_5rujrkvh_1_1.bkp
/asmbck/backup_area/20191219_5qujrkve_1_1.bkp

B. Copy initASMDB.ora from asmdb-host to fsdb-host with New Name

oracle > scp /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initASMDB.ora oracle@asmdb-host_ip:/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/initFSDB.ora

C. Copy RMAN Backup Files to fsdb-host
oracle > scp /asmbck/backup_area/* oracle@asmdb-host_ip:/home/oracle/backup_area/.

D. Informations that We Need for Restore from Source Database:

D.1. DBID

rman target /
-
ASMDB (DBID=2643346189)

Save this value. Because we use this value at restore ASM database to FILE SYSTEM database.

D.2. Lists of Data Files and Temporary Files

RMAN > report schema;
-
Datafiles
-
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- -------------------------------------------
1    5170     SYSTEM               YES     +DATA/ASMDB/DATAFILE/system.300.983287017
2    30720    TS_NAME_1            NO      +DATA/ASMDB/DATAFILE/TS_NAME_1_DBF_1.dbf
3    25620    SYSAUX               NO      +DATA/ASMDB/DATAFILE/sysaux.313.983287041
4    32760    UNDOTBS1             YES     +DATA/ASMDB/DATAFILE/undotbs1.314.983287067
5    5        USERS                NO      +DATA/ASMDB/DATAFILE/users.317.983287425
6    32760    TS_NAME_1            NO      +DATA/ASMDB/DATAFILE/TS_NAME_1_DBF_2.dbf
7    32758    TS_NAME_1            NO      +DATA/ASMDB/DATAFILE/TS_NAME_1_DBF_3.dbf
8    32714    TS_NAME_1            NO      +DATA/ASMDB/DATAFILE/TS_NAME_1_DBF_4.dbf
9    32753    TS_NAME_1            NO      +DATA/ASMDB/DATAFILE/TS_NAME_1_DBF_5.dbf
10   30720    TS_NAME_2            NO      +DATA/ASMDB/DATAFILE/TS_NAME_2_DBF_1.dbf
11   29192    TS_NAME_2            NO      +DATA/ASMDB/DATAFILE/TS_NAME_2_DBF_2.dbf
12   30629    TS_NAME_2            NO      +DATA/ASMDB/DATAFILE/TS_NAME_2_DBF_3.dbf
13   15800    TS_NAME_3            NO      +DATA/ASMDB/DATAFILE/TS_NAME_3_DBF_1.dbf
14   10064    TS_NAME_3            NO      +DATA/ASMDB/DATAFILE/TS_NAME_3_DBF_2.dbf
15   4100     TS_NAME_4            NO      +DATA/ASMDB/DATAFILE/TS_NAME_4_DBF_1.dbf
16   200      TS_NAME_5            NO      +DATA/ASMDB/DATAFILE/TS_NAME_5_DBF_1.dbf
17   200      TS_NAME_6            NO      +DATA/ASMDB/DATAFILE/TS_NAME_6_DBF_1.dbf
18   200      TS_NAME_7            NO      +DATA/ASMDB/DATAFILE/TS_NAME_7_DBF_1.dbf
19   32712    TS_NAME_1            NO      +DATA/ASMDB/DATAFILE/TS_NAME_1_DBF_6.dbf
-
Temporary Files
-
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- ---------------------------------------
1    32767    TEMP                 32767       +DATA/ASMDB/TEMPFILE/temp.315.983287147

D.3. Redo Log Files

SQL > select group#, member from v$logfile;
-
GROUP#			MEMBER
----------		-------------------------------------------
3			+RECO/ASMDB/ONLINELOG/group_3.295.983287119
2			+RECO/ASMDB/ONLINELOG/group_2.294.983287119
1			+RECO/ASMDB/ONLINELOG/group_1.293.983287119

II. TARGET SIDE

A. Prepare FILE SYSTEM Server for Restore

A.1. Install Oracle Database 12.2.0.1.0 software only

A.2. Create Listener

A.3. Edit .bash_profile File

When you editing .bash_profile, you must set ORACLE_SID=FSDB.

# .bash_profile

ORACLE_BASE=/home/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_1
ORACLE_SID=FSDB
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin

export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export PATH

A.4. Create tnsnames.ora File

When you create tnsnames.ora, you must use FSDB specifications.

FSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = fsdb-host)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FSDB)
    )
  )

A.5. Edit initFSDB.ora File

IMPORTANT!

When you editing initFSDB.ora file, you must set only “db_name=ASMDB” and you must set the other parameters with FSDB specifications.

A.5.1. My initFSDB.ora File:

db_name=ASMDB
control_files=(/home/oracle/oradb/FSDB/control01.ctl,/home/oracle/oradb/FSDB/control02.ctl,/home/oracle/oradb/FSDB/control03.ctl)

sga_max_size=5g
sga_target=5g
pga_aggregate_target=750m

db_block_size=8192
open_cursors=2048
db_domain=''
job_queue_processes=10
compatible=12.2.0.1.0
nls_language='AMERICAN'
nls_length_semantics=CHAR
nls_territory='AMERICA'
processes=300
sessions=1000

os_authent_prefix=''
remote_login_passwordfile=EXCLUSIVE

undo_management=AUTO
undo_retention=3600
undo_tablespace='UNDOTBS1'
dml_locks=800
OPTIMIZER_INDEX_COST_ADJ=10

B. Restore Database

B.1. Restore Control Files:

rman target /
-
RMAN> set dbid=2643346189 // This value is ASMDB's DBID
RMAN> startup nomount;
RMAN> restore controlfile from '/home/oracle/backup_area/20191219_5qujrkve_1_1.bkp'

B.2. Create RMAN Catalog

SQL > alter database mount;
RMAN > catalog start with "/home/oracle/backup_area";

B.3. Restore Database and Rename Files

rman target /
-
run {
SET NEWNAME FOR DATABASE TO '/home/oracle/oradb/FSDB/%b';
SET NEWNAME FOR tempfile 1 TO  '/home/oracle/oradb/FSDB/%b';
SQL "ALTER DATABASE RENAME FILE ''+RECO/ASMDB/ONLINELOG/group_3.295.983287119'' to ''/home/oracle/oradb/FSDB/group_3.295.983287119''" ;
SQL "ALTER DATABASE RENAME FILE ''+RECO/ASMDB/ONLINELOG/group_2.294.983287119'' to ''/home/oracle/oradb/FSDB/group_2.294.983287119''" ;
SQL "ALTER DATABASE RENAME FILE ''+RECO/ASMDB/ONLINELOG/group_1.293.983287119'' to ''/home/oracle/oradb/FSDB/group_1.293.983287119''" ;
restore database;
switch datafile all;
switch tempfile all;
}

B.4. Recover Database and Open Resetlogs:

RMAN > recover database;
SQL > alter database open resetlogs;

C. Control the New Database

C.1. Log File Names

SQL > select member from v$logfile;
-
MEMBER
-----------------------------------------------
'/home/oracle/oradb/FSDB/group_3.295.983287119'
'/home/oracle/oradb/FSDB/group_2.294.983287119'
'/home/oracle/oradb/FSDB/group_1.293.983287119'

C.2. DB_NAME, DB_UNIQUE_NAME and DBID

SQL > select name, db_unique_name, dbid from v$database;
-
NAME      DB_UNIQUE_NAME                       DBID
--------- ------------------------------ ----------
ASMDB     ASMDB                          2643346189

D. Change New Database Specifications

D.1. Change DB_NAME, DB_UNIQUE_NAME and DBID

SQL > shutdown immediate;
SQL > startup mount;
oracle > nid target=sys dbname=FSDB

After this step database will shutdown.

D.2. Change db_name in initFSDB.ora

db_name=FSDB

D.3. Recreate Password File

oracle > orapwd file=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/orapwFSDB password="SYS_PASSWORD" entries=5

E. Startup Database

SQL > startup nomount;
SQL > create spfile from pfile;
SQL > shutdown immediate;
SQL > startup mount;
SQL > alter database open resetlogs;

F. Last Control

F.1. DB_NAME, DB_UNIQUE_NAME and DBID

SQL > select name, db_unique_name, dbid from v$database;
-
NAME      DB_UNIQUE_NAME                       DBID
--------- ------------------------------ ----------
FSDB      FSDB                            736121565

F.2. Control Files

SQL > SELECT NAME FROM V$CONTROLFILE;
-
NAME
-------------------------------------
/home/oracle/oradb/FSDB/control01.ctl
/home/oracle/oradb/FSDB/control02.ctl
/home/oracle/oradb/FSDB/control03.ctl

F.3. Data Files

SQL > select file_name from dba_data_files;
-
FILE_NAME
----------------------------------------------
/home/oracle/oradb/FSDB/system.300.983287017
/home/oracle/oradb/FSDB/sysaux.313.983287041
/home/oracle/oradb/FSDB/undotbs1.314.983287067
/home/oracle/oradb/FSDB/users.317.983287425
/home/oracle/oradb/FSDB/TS_NAME_1_DBF_1.dbf
/home/oracle/oradb/FSDB/TS_NAME_1_DBF_2.dbf
/home/oracle/oradb/FSDB/TS_NAME_1_DBF_3.dbf
/home/oracle/oradb/FSDB/TS_NAME_1_DBF_4.dbf
/home/oracle/oradb/FSDB/TS_NAME_1_DBF_5.dbf
/home/oracle/oradb/FSDB/TS_NAME_1_DBF_6.dbf
/home/oracle/oradb/FSDB/TS_NAME_2_DBF_1.dbf
/home/oracle/oradb/FSDB/TS_NAME_2_DBF_2.dbf
/home/oracle/oradb/FSDB/TS_NAME_2_DBF_3.dbf
/home/oracle/oradb/FSDB/TS_NAME_3_DBF_1.dbf
/home/oracle/oradb/FSDB/TS_NAME_3_DBF_2.dbf
/home/oracle/oradb/FSDB/TS_NAME_4_DBF_1.dbf
/home/oracle/oradb/FSDB/TS_NAME_5_DBF_1.dbf
/home/oracle/oradb/FSDB/TS_NAME_6_DBF_1.dbf
/home/oracle/oradb/FSDB/TS_NAME_7_DBF_1.dbf

F.4. Log Files

SQL > select member from v$logfile;
-
MEMBER
-----------------------------------------------
'/home/oracle/oradb/FSDB/group_3.295.983287119'
'/home/oracle/oradb/FSDB/group_2.294.983287119'
'/home/oracle/oradb/FSDB/group_1.293.983287119'

F.5. Temp Files

SQL > select name from v$tempfile;
-
NAME
------------------------------------------
/home/oracle/oradb/FSDB/temp.315.983287147

Everything is perfect.

References:

Oracle

ACFSASMFILE SYSTEMODARMAN