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
Side | Platform | Database Version | Oracle SID | Database Location | Backup Location | Hostname |
---|---|---|---|---|---|---|
Source | ODA X7-2M | 12.2.0.1.0 | ASMDB | DATA (DiskGroup) | /asmbck/backup_area | asmdb-host |
Target | Oracle Linux 7.6 x86_64 | 12.2.0.1.0 | FSDB | home/oracle/oradb | /home/oracle/backup_area | fsdb-host |
A. SOURCE SIDE
A.1. 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.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.1.2. Create Backup Location
root > mkdir -p /asmbck/backup_area
root > chown -R oracle:oinstall /asmbck/backup_area
A.1.3. RMAN Backup
You can use the followig script for RMAN backup.
oracle > rman target /
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.1.3.1. 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
A.2. 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
A.3. Copy RMAN Backup Files to fsdb-host
oracle > scp /asmbck/backup_area/* oracle@asmdb-host_ip:/home/oracle/backup_area/.
A.4. Informations that We Need for Restore from Source Database:
A.4.1. DBID
oracle > rman target /
ASMDB (DBID=2643346189)
Save this value. Because we use this value at restore ASM database to FILE SYSTEM database.
A.4.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
A.4.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
B. TARGET SIDE
B.1. Prepare FILE SYSTEM Server for Restore
B.1.1. Install Oracle Database 12.2.0.1.0 software only
B.1.2. Create Listener
B.1.3. Edit ".bash_profile" File
When you editing .bash_profile, you must set ORACLE_SID=FSDB.
oracle > vi .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
B.1.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)
)
)
B.1.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.
B.1.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.2. Restore Database
B.2.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.2. Create RMAN Catalog
SQL > alter database mount;
RMAN > catalog start with "/home/oracle/backup_area";
B.2.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.2.4. Recover Database and Open Resetlogs
RMAN > recover database;
SQL > alter database open resetlogs;
B.3. Control the New Database
B.3.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'
B.3.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
B.4. Change New Database Specifications
B.4.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.
B.4.2. Change "db_name" in "initFSDB.ora"
db_name=FSDB
B.4.3. Recreate Password File
oracle > orapwd file=/home/oracle/app/oracle/product/12.2.0/dbhome_1/dbs/orapwFSDB password="SYS_PASSWORD" entries=5
B.5. Startup Database
SQL > startup nomount;
SQL > create spfile from pfile;
SQL > shutdown immediate;
SQL > startup mount;
SQL > alter database open resetlogs;
B.6. Last Control
B.6.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
B.6.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
B.6.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
B.6.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'
B.6.5. Temp Files
SQL > select name from v$tempfile;
NAME
------------------------------------------
/home/oracle/oradb/FSDB/temp.315.983287147
Everything is perfect.
References: 1. https://gavinsoorma.com/2013/02/restoring-a-asm-backup-to-non-asm-and-restoring-from-rac-to-single-instance/ 2. http://gablovesoracle.blogspot.com/2012/08/rman-restoration-to-new-server-with.html 3. http://www.br8dba.com/rman-database-restore-from-asm-to-file-system/