RMAN Database Restore ASM

Introduction

In this article, we will see how to restore a full database from an ASM RAC database  to a non-RAC database having Oracle restart the standalone grid infrastructure (RMAN database restore ASM).

And we assume that there is a tape backup that needs to be restored to the target non-RAC database having ASM disk storage.

Below are all the steps to restore oracle database:

Step 1: Connect to the database

Oracle@wbuatdr1:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 12 09:19:20 2023

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Step 2: Shutdown the database

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

3: Start up mount from pfile with restrict mode

SQL> startup mount restrict pfile='/export/home/oracle/pfile.ora';
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                  5284640 bytes
Variable Size            6308240608 bytes
Database Buffers         3.6507E+10 bytes
Redo Buffers              128925696 bytes
Database mounted

4 : Drop the database

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

5: Check ASM disk space availability

oracle@wbuatdr1:~$ su - grid
Password:
Oracle Corporation      SunOS 5.11      11.3    September 2015
grid@wbuatdr1:~$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  8388608   5631824  5626936                0         5626936              0             Y  DATA/
MOUNTED  EXTERN  N         512   4096  1048576    511991   436067                0          436067              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576     30711    30651                0           30651              0             N  REDO1/
MOUNTED  EXTERN  N         512   4096  1048576     30711    30651                0           30651              0             N  REDO2/

6: Delete Existing archive logs and auto backup from ASM disk, if any

ASMCMD> cd FRA/
ASMCMD> ls
PRODDB/
ASMCMD> cd PRODDB/ ARCHIVELOG/
2023_03_14/
2023_03_31/
2023_04_10/
ASMCMD> rm –rf  2023_03_14/  2023_03_31/    2023_04_10/

7: Startup nomount pfile

oracle@wbuatdr1:~$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 12 09:31:21 2023

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> startup nomount pfile='/export/home/oracle/pfile.ora';
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                  5284640 bytes
Variable Size            6308240608 bytes
Database Buffers         3.6507E+10 bytes
Redo Buffers              128925696 bytes

8: Restore controlfile from backup

oracle@wbuatdr1:~$  rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Apr 12 14:55:28 2023

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODB(DBID=88272031)

RMAN>restore controlfile from '/Backup/Wednesday/29_CTLFILE';
restore controlfile from '/backup/Wednesday/29_CTLFILE';
Starting restore at 12-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2836 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/12/2023 09:34:55
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

9: Give permission for the above backup directory to fix the above errors.

oracle@wbuatdr1:~$ su - sol
Password:
Warning: 1 failed authentication attempt at Wed Dec 31 03:00 1969 since last successful authentication.
Oracle Corporation      SunOS 5.11      11.3    September 2015
sol@wbuatdr1:~$ su -
Password:
Oracle Corporation      SunOS 5.11      11.3    September 2015

root@wbuatdr1:~# cd /backup/Wednesday
root@wbuatdr1:/backup/Wednesday# chmod 777 *

10: Now run the controlfile restore command

oracle@wbuatdr1:~$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Apr 12 09:37:51 2023

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODDB(not mounted)

RMAN>  restore controlfile from '/backup/29_CTLFILE';

Starting restore at 12-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2648 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/PRODDB/CONTROLFILE/current.276.1133948293
Finished restore at 12-APR-23

Recovery Manager complete.

11: Edit the pfile with the restored controlfile and save it.

control_files='+DATA/WFCPROD/CONTROLFILE/current.276.1133948293'
cursor_sharing='FORCE'
db_block_size=8192
db_cache_advice='OFF'
db_create_file_dest='+DATA'
db_domain=''
db_file_name_convert='+DATA'
db_files=500
db_name='wfcprod'
db_recovery_file_dest='+FRA'
db_recovery_file_dest_size=2000G
diagnostic_dest='/u01/app/oracle'

12 : Mount database and backup catalog

RMAN> alter database mount;

using target database control file instead of recovery catalog
Statement processed

RMAN> catalog start with '/backup/Wednesday';
Starting implicit crosscheck backup at 12-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2648 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=191 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=381 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=570 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=759 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=948 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=1137 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=1327 device type=DISK
Crosschecked 2012 objects
Finished implicit crosscheck backup at 12-APR-23

Starting implicit crosscheck copy at 12-APR-23
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
Crosschecked 1966 objects
Finished implicit crosscheck copy at 12-APR-23

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +FRA/PRODDB/AUTOBACKUP/2022_06_25/n_1108285211.353.1108285217

searching for all files that match the pattern /backup/Wednesday

List of Files Unknown to the Database
=====================================
File Name: /backup/29_FULL_BACKUP_na1o9j97_1_1_1132776743_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_n01o9j94_1_1_1132776740_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_nm1o9k5j_1_1_1132777651_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_nf1o9k5f_1_1_1132777647_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_nq1o9k5n_1_1_1132777655_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_n71o9j96_1_1_1132776742_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_n31o9j95_1_1_1132776741_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_mt1o9j94_1_1_1132776740_20230329

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /backup/Wednesday/29_FULL_BACKUP_na1o9j97_1_1_1132776743_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_n01o9j94_1_1_1132776740_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_na1o9j97_1_1_1132776743_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_n01o9j94_1_1_1132776740_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_nm1o9k5j_1_1_1132777651_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_nf1o9k5f_1_1_1132777647_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_nq1o9k5n_1_1_1132777655_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_n71o9j96_1_1_1132776742_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_n31o9j95_1_1_1132776741_20230329
File Name: /backup/Wednesday/29_FULL_BACKUP_mt1o9j94_1_1_1132776740_20230329

12: Crosscheck backup, delete expired backup, crosscheck archivelog

RMAN> crosscheck backup;
RMAN> delete expired backup;
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

13: Restore database

We can restore the database either using the ‘restore database’ command or using the restore shell script:

RMAN> restore database;

OR use restore shell script(restore.sh) and the restore.sh file contains the following lines of code:

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/
export ORACLE_SID=UATDB /u01/app/oracle/product/12.1.0/dbhome_1/bin/rman@'/export/home/oracle/restore.cmd;’

And the restore.cmd contains the following lines of code:

connect target / 
run { 
restore database;
 }

And finally we run restore.sh script command:

[oracle@wbuatdr1~] nohup ./restore.sh &

14: Recover database

RMAN > recover database;

 Starting recover at 12-APR-23
 using channel ORA_DISK_1 
 using channel ORA_DISK_2 
 using channel ORA_DISK_3 
 using channel ORA_DISK_4 
 using channel ORA_DISK_5 
 using channel ORA_DISK_6 
 using channel ORA_DISK_7 
 using channel ORA_DISK_8 
 starting media recovery archived log for thread 1 with sequence 4531 is already on disk as file +FRA/WFCPROD/ARCHIVELOG/2022_12_05/thread_1_seq_4531.540.1122626343 
 channel ORA_DISK_1: starting archived log restore to default destination 
 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=4530 
 channel ORA_DISK_1: reading from backup piece /wednesday/22082022/29_FULL_BACKUP_pe1dr1r4_1_1_112181437_22082022 
 channel ORA_DISK_1: piece handle=/Wednesday/22082022/29_FULL_BACKUP_pe1dr1r4_1_1_1121814372_22082022 tag=TAG20221126T230246 
 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:11:55 archived log file name=+FRA/PRODDB/ARCHIVELOG/2022_12_05/thread_1_seq_4530.408.1122626555 thread=1 sequence=4530 channel default: deleting archived log(s) archived log file name=+FRA/PRODDB/ARCHIVELOG/2022_12_07/thread_1_seq_4530.408.1122626555 RECID=127915 STAMP=1122627260 archived log file name=+FRA/UATDB/ARCHIVELOG/2022_12_07/thread_1_seq_4531.540.1122626343 thread=1 sequence=4531 unable to find archived log archived log thread=1 sequence=4532
 RMAN-00571: =========================================================== 
 RMAN-00569: =============== 
 ERROR MESSAGE STACK FOLLOWS =============== 
 RMAN-00571: ===========================================================
 RMAN-03002: failure of recover command at 12/07/2022 08:55:19 
 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4532 and starting SCN of 135221823820
 RMAN> alter database open resetlogs; 
 Statement processed 
 RMAN> select status from v$instance;
 STATUS ------------ 
 OPEN 
 RMAN>

15: Create spfile from pfile

oracle@wbuatdr1:~$ sqlplus / as sysdba 
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 7 16:19:20 2022 Copyright (c) 1982, 2014,
 racle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options

SQL> Create spfile from pfile=’/export/home/oracle/pfile.ora’;
 created.

16: shutdown and start up the database

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down

SQL >  startup ;
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                  5284640 bytes
Variable Size            6308240608 bytes
Database Buffers         3.6507E+10 bytes
Redo Buffers              128925696 bytes

Database opened.