RMAN - Restore Scenario

byShohanur Rahaman -

 

r  Restore – Scenario
 
o   Nomount – Spfile
o   Mount – Controlfile
o   Open – Datafile | Archivelog
 
 
r  Scenario 01: Drop full Database | Restore from Level 0
 
[oracle@dbserver ~]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 30 23:25:02 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MAXCDB (DBID=2565367265)
 
RMAN> RUN {
ALLOCATE CHANNEL chl1 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl2 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl3 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/u01/backup/%d_D_LEVEL0_%T_%u_s%s_p%p'
INCREMENTAL LEVEL 0 DATABASE
CURRENT CONTROLFILE
FORMAT '/u01/backup/%d_C_LEVEL0_%T_%u'
SPFILE
FORMAT '/u01/backup/%d_S_LEVEL0_%T_%u'
PLUS ARCHIVELOG
FORMAT '/u01/backup/%d_A_LEVEL0_%T_%u_s%s_p%p';
RELEASE CHANNEL chl1;
RELEASE CHANNEL chl2;
RELEASE CHANNEL chl3;
}
using target database control file instead of recovery catalog
allocated channel: chl1
channel chl1: SID=281 device type=DISK
allocated channel: chl2
channel chl2: SID=49 device type=DISK
allocated channel: chl3
channel chl3: SID=283 device type=DISK
Starting backup at 30-JUL-24
current log archived
released channel: chl1
released channel: chl2
released channel: chl3
 
 
r  Drop Database [Delete all the files like controlfile, datafile, spfile etc.]

[oracle@dbserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 31 10:08:54 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
 
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount restrict; [You can’t drop the database when it is up and running]
ORACLE instance started.
Total System Global Area 2365584784 bytes
Fixed Size                  9180560 bytes
Variable Size             536870912 bytes
Database Buffers         1811939328 bytes
Redo Buffers                7593984 bytes
Database mounted.
 
SQL> drop database;
Database dropped.
 

r  Restoration Database:

[oracle@prod ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 16 10:32:35 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
 
RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initorclcdb.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area    1073737800 bytes
Fixed Size                     8904776 bytes
Variable Size                276824064 bytes
Database Buffers             784334848 bytes
Redo Buffers                   3674112 bytes

RMAN> restore spfile from '/u01/rman/ORCLCDB_S_LEVEL0_20240316_6b2lr2j6_s203_p1';

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
 
RMAN> startup nomount;

connected to target database (not started)
Oracle instance started
Total System Global Area    2365584784 bytes
Fixed Size                     9180560 bytes
Variable Size                536870912 bytes
Database Buffers            1811939328 bytes
Redo Buffers                   7593984 bytes
 
RMAN> restore controlfile from '/u01/rman/ORCLCDB_C_LEVEL0_20240316_692lr2hs_s201_p1';

Starting restore at 16-MAR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCLCDB/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/control02.ctl
Finished restore at 16-MAR-24
 
RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed
 
RMAN> restore database;
Starting restore at 16-MAR-24
Starting implicit crosscheck backup at 16-MAR-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
Crosschecked 15 objects
Finished implicit crosscheck backup at 16-MAR-24
 
RMAN> recover database;
Starting recover at 16-MAR-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2024_03_16/o1_mf_1_4_lzb6w8v4_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2024_03_16/o1_mf_1_4_lzb6w8v4_.arc thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/16/2024 10:48:31
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 5 and starting SCN of 5565499
 
RMAN> alter database open resetlogs;
Statement processed
 
 
r  Scenario 02: Drop full Database | Restore from Level 1
 
[oracle@dbserver ~]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 30 23:25:02 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MAXCDB (DBID=2565367265)
 
RMAN> RUN {
ALLOCATE CHANNEL chl1 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl2 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl3 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/u01/backup/%d_D_LEVEL0_%T_%u_s%s_p%p'
INCREMENTAL LEVEL 0 DATABASE
CURRENT CONTROLFILE
FORMAT '/u01/backup/%d_C_LEVEL0_%T_%u'
SPFILE
FORMAT '/u01/backup/%d_S_LEVEL0_%T_%u'
PLUS ARCHIVELOG
FORMAT '/u01/backup/%d_A_LEVEL0_%T_%u_s%s_p%p';
RELEASE CHANNEL chl1;
RELEASE CHANNEL chl2;
RELEASE CHANNEL chl3;
}
 
[oracle@dbserver ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 31 12:31:12 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
 
SQL> alter session set container=maxpdb;
Session altered.
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/MAXCDB/maxpdb/sysaux01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/system01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/undotbs01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/users01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/apex01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/users02.dbf
 
6 rows selected.
 
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/MAXCDB/maxpdb/users02.dbf' size 100m autoextend on;
 
Tablespace Created
 
RMAN> RUN {
ALLOCATE CHANNEL chl1 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl2 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl3 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/u01/backup/%d_D_LEVEL1_LEVEL1_%T_%u_s%s_p%p'
INCREMENTAL LEVEL 1 DATABASE
CURRENT CONTROLFILE
FORMAT '/u01/backup/%d_C_LEVEL1_%T_%u'
SPFILE
FORMAT '/u01/backup/%d_S_LEVEL1_%T_%u'
PLUS ARCHIVELOG
FORMAT '/u01/backup/%d_A_LEVEL1_%T_%u_s%s_p%p';
RELEASE CHANNEL chl1;
RELEASE CHANNEL chl2;
RELEASE CHANNEL chl3;
}
 
using target database control file instead of recovery catalog
allocated channel: chl1
channel chl1: SID=283 device type=DISK
allocated channel: chl2
channel chl2: SID=272 device type=DISK
allocated channel: chl3
channel chl3: SID=44 device type=DISK
Starting backup at 31-JUL-24
current log archived
released channel: chl1
released channel: chl2
released channel: chl3
 
[oracle@dbserver ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 31 12:36:48 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
 
SQL> shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount restrict;

ORACLE instance started.
Total System Global Area 2365584784 bytes
Fixed Size                  9180560 bytes
Variable Size             536870912 bytes
Database Buffers         1811939328 bytes
Redo Buffers                7593984 bytes
Database mounted.
 
SQL> drop database;
Database dropped.
 
 
r  Restore Database:
 
[oracle@dbserver ~]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 31 12:40:58 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
 
RMAN> startup nomount;
 
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initmaxcdb.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area    1073739904 bytes
Fixed Size                     8947840 bytes
Variable Size                276824064 bytes
Database Buffers             780140544 bytes
Redo Buffers                   7827456 bytes

RMAN> restore spfile from '/u01/backup/MAXCDB_S_LEVEL1_20240731_24319qpc';  
             
Starting restore at 31-JUL-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/MAXCDB_S_LEVEL1_20240731_24319qpc
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 31-JUL-24
 
RMAN> shutdown immediate;
 
Oracle instance shut down
 
RMAN> startup nomount;
 
connected to target database (not started)
Oracle instance started
Total System Global Area    2365584784 bytes
Fixed Size                     9180560 bytes
Variable Size                536870912 bytes
Database Buffers            1811939328 bytes
Redo Buffers                   7593984 bytes
 
RMAN> restore controlfile from '/u01/backup/MAXCDB_C_LEVEL1_20240731_23319qpb';
 
Starting restore at 31-JUL-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/MAXCDB/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/MAXCDB/control02.ctl
Finished restore at 31-JUL-24
 
RMAN> alter database mount;
 
released channel: ORA_DISK_1
Statement processed
 
RMAN> restore database;
 
Starting restore at 31-JUL-24
Starting implicit crosscheck backup at 31-JUL-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 23 objects
Finished implicit crosscheck backup at 31-JUL-24
Starting implicit crosscheck copy at 31-JUL-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-JUL-24
searching for all files in the recovery area
cataloging files...
cataloging done
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 31-JUL-24
 
RMAN> recover database;
 
Starting recover at 31-JUL-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/MAXCDB/system01.dbf
destination for restore of datafile 00004: 
archived log file name=/u01/archive/1_2_1175771414.dbf thread=1 sequence=2
archived log file name=/u01/archive/1_3_1175771414.dbf thread=1 sequence=3
archived log file name=/u01/archive/1_4_1175771414.dbf thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/31/2024 12:47:17
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 5 and starting SCN of 3056593
 
RMAN> alter database open resetlogs;
 
Statement processed
 
 
r  Check deleted datafile:
 
[oracle@dbserver ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 31 12:49:23 2024
Version 19.22.0.0.0
 
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
 
SQL> alter session set container=maxpdb;
 
Session altered.
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/MAXCDB/maxpdb/system01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/sysaux01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/undotbs01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/users01.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/users02.dbf
/u01/app/oracle/oradata/MAXCDB/maxpdb/apex01.dbf
 
6 rows selected.


r  Scenario 03: Point-in-Time Recovery (PITR)
 
o   Achieved in three levels:
§  Database [DBPITR]
§  Tablespace [TSPITR]
§  Table
o   Required recovery Point:
§  SCN
§  Log sequence
§  Restore point
§  Time
 
[oracle@dbserver ~]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 31 16:58:14 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MAXCDB (DBID=2565367265)
 
RMAN> RUN {
ALLOCATE CHANNEL chl1 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl2 TYPE DISK MAXPIECESIZE 10G;
ALLOCATE CHANNEL chl3 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/u01/backup/%d_D_LEVEL0_%T_%u_s%s_p%p'
INCREMENTAL LEVEL 0 DATABASE
CURRENT CONTROLFILE
FORMAT '/u01/backup/%d_C_LEVEL0_%T_%u'
SPFILE
FORMAT '/u01/backup/%d_S_LEVEL0_%T_%u'
PLUS ARCHIVELOG
FORMAT '/u01/backup/%d_A_LEVEL0_%T_%u_s%s_p%p';
RELEASE CHANNEL chl1;
RELEASE CHANNEL chl2;
RELEASE CHANNEL chl3;
}

Starting backup at 31-JUL-24
current log archived
channel chl1: starting archived log backup set
channel chl1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=20 STAMP=1175775292
channel chl1: starting piece 1 at 31-JUL-24
channel chl2: starting archived log backup set
 name=/u01/app/oracle/oradata/MAXCDB/sysaux01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/MAXCDB/users01.dbf
 
[oracle@dbserver ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 31 20:12:38 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
 
SQL> select TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
31-JUL-2024 20:15:53
 
SQL> alter system switch logfile;
 
System altered.
 
SQL> alter session set container=maxpdb;
 
Session altered.
 
SQL> drop table HR.EMPLOYEES cascade constraints;
 
Table dropped.
 
SQL> alter system switch logfile;
 
System altered.
 
[oracle@dbserver ~]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jul 31 20:36:45 2024
Version 19.22.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: MAXCDB (DBID=2565367265)
 
RMAN> shutdown immediate;
 
using target database control file instead of recovery catalog
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    2365584784 bytes
Fixed Size                     9180560 bytes
Variable Size                536870912 bytes
Database Buffers            1811939328 bytes
Redo Buffers                   7593984 bytes
 
RMAN> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
 
Statement processed
 
RMAN> select sysdate from dual;
 
SYSDATE
--------------------
31-JUL-2024 20:39:39
 
 
******************************************
RUN {
SET UNTIL TIME 'DD-MON-YYYY HH24:MI:SS';
RESTORE DATABASE;
RECOVER DATABASE;
}
******************************************
 
RMAN> RUN {
SET UNTIL TIME '31-JUL-2024 20:39:39';
RESTORE DATABASE;
RECOVER DATABASE;
}
 
executing command: SET until clause
Starting restore at 31-JUL-24
using channel ORA_DISK_1
media recovery complete, elapsed time: 00:00:03
Finished recover at 31-JUL-24
 
RMAN> alter database open resetlogs;
 
Statement processed
 
 

Tags

You may like these posts

Previous Post Next Post

نموذج الاتصال