Connect rman and Take level 0 backup
[oratest@oracle dbs]$ rman target/ Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 00:42:53 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1608118455)
RMAN> backup incremental level 0 database plus archivelog; Starting backup at 09-SEP-21 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=471 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=21 STAMP=1082265443 input archived log thread=1 sequence=2 RECID=22 STAMP=1082354497 input archived log thread=1 sequence=3 RECID=23 STAMP=1082412592 input archived log thread=1 sequence=4 RECID=24 STAMP=1082470208 input archived log thread=1 sequence=5 RECID=25 STAMP=1082554248 input archived log thread=1 sequence=6 RECID=26 STAMP=1082640636 input archived log thread=1 sequence=7 RECID=27 STAMP=1082677257 input archived log thread=1 sequence=9 RECID=29 STAMP=1082766943 input archived log thread=1 sequence=10 RECID=30 STAMP=1082766950 input archived log thread=1 sequence=11 RECID=31 STAMP=1082766986 input archived log thread=1 sequence=12 RECID=32 STAMP=1082766988 input archived log thread=1 sequence=13 RECID=33 STAMP=1082766991 input archived log thread=1 sequence=14 RECID=34 STAMP=1082767405 channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1f08je1e_1_1 tag=TAG20210909T004325 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=13 RECID=9 STAMP=1081792908 input archived log thread=1 sequence=14 RECID=10 STAMP=1081863051 input archived log thread=1 sequence=15 RECID=11 STAMP=1081953019 input archived log thread=1 sequence=16 RECID=12 STAMP=1082066461 input archived log thread=1 sequence=17 RECID=19 STAMP=1082263361 input archived log thread=1 sequence=18 RECID=20 STAMP=1082263367 input archived log thread=1 sequence=19 RECID=18 STAMP=1082263356 channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1g08je36_1_1 tag=TAG20210909T004325 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47 Finished backup at 09-SEP-21 Starting backup at 09-SEP-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/data02.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/data01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1h08je4m_1_1 tag=TAG20210909T004510 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06 Finished backup at 09-SEP-21 Starting backup at 09-SEP-21 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=15 RECID=35 STAMP=1082767577 channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1i08je6p_1_1 tag=TAG20210909T004617 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 09-SEP-21 Starting Control File and SPFILE Autobackup at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/c-1608118455-20210909-00 comment=NONE Finished Control File and SPFILE Autobackup at 09-SEP-21 Check backup summary and note the level 0 backup TAG
RMAN> list backup of database summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag --- -- -- - ----------- --------------- ------- ------- ---------- --- 1 B F A DISK 11-AUG-21 1 1 NO TAG20210811T222830 4 B F A DISK 11-AUG-21 1 1 NO TAG20210811T223126 7 B F A DISK 11-AUG-21 1 1 NO TAG20210811T223345 10 B F A DISK 11-AUG-21 1 1 NO TAG20210811T224016 12 B F A DISK 11-AUG-21 1 1 NO TAG20210811T224942 28 B F A DISK 12-AUG-21 1 1 NO TAG20210812T223307 30 B 0 A DISK 12-AUG-21 1 1 NO TAG20210812T224824 32 B 1 A DISK 12-AUG-21 1 1 NO TAG20210812T225022 34 B 1 A DISK 12-AUG-21 1 1 NO TAG20210812T225145 36 B F A DISK 25-AUG-21 1 1 NO TAG20210825T220051 40 B F A DISK 03-SEP-21 1 1 NO TAG20210903T044703 47 B 0 A DISK 09-SEP-21 1 1 NO TAG20210909T004510 RMAN> exit
Once Level 0 is completed creating a user and table insert the record for the purpose of level 1 Backup
[oracle@oracle ~]$ export ORACLE_SID=orcl [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:42:05 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
SQL> create user india identified by india; User created. SQL> grant connect, resource to india; Grant succeeded. SQL> alter user india default tablespace users quota unlimited on users; User altered.
SQL> conn india/india Connected. create table test (no number(10),name varchar2(20)); SQL> insert into test values(1,'one'); 1 row created. SQL> insert into test values(2,'Two'); 1 row created. SQL> insert into test values(3,'Three'); 1 row created. SQL> insert into test values(4,'Four'); 1 row created. SQL> commit; Commit complete.
Connect RMAN And Take Level 1 Backup
[oratest@oracle dbs]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 01:00:32 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1608118455)
RMAN> backup incremental level 1 database plus archivelog; Starting backup at 09-SEP-21 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=21 STAMP=1082265443 input archived log thread=1 sequence=2 RECID=22 STAMP=1082354497 input archived log thread=1 sequence=3 RECID=23 STAMP=1082412592 input archived log thread=1 sequence=4 RECID=24 STAMP=1082470208 input archived log thread=1 sequence=5 RECID=25 STAMP=1082554248 input archived log thread=1 sequence=6 RECID=26 STAMP=1082640636 input archived log thread=1 sequence=7 RECID=27 STAMP=1082677257 input archived log thread=1 sequence=9 RECID=29 STAMP=1082766943 input archived log thread=1 sequence=10 RECID=30 STAMP=1082766950 input archived log thread=1 sequence=11 RECID=31 STAMP=1082766986 input archived log thread=1 sequence=12 RECID=32 STAMP=1082766988 input archived log thread=1 sequence=13 RECID=33 STAMP=1082766991 input archived log thread=1 sequence=14 RECID=34 STAMP=1082767405 input archived log thread=1 sequence=15 RECID=35 STAMP=1082767577 input archived log thread=1 sequence=16 RECID=36 STAMP=1082768454 channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1k08jf27_1_1 tag=TAG20210909T010055 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=13 RECID=9 STAMP=1081792908 input archived log thread=1 sequence=14 RECID=10 STAMP=1081863051 input archived log thread=1 sequence=15 RECID=11 STAMP=1081953019 input archived log thread=1 sequence=16 RECID=12 STAMP=1082066461 input archived log thread=1 sequence=17 RECID=19 STAMP=1082263361 input archived log thread=1 sequence=18 RECID=20 STAMP=1082263367 input archived log thread=1 sequence=19 RECID=18 STAMP=1082263356 channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1l08jf3u_1_1 tag=TAG20210909T010055 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47 Finished backup at 09-SEP-21 Starting backup at 09-SEP-21 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/data02.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/data01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1m08jf5e_1_1 tag=TAG20210909T010237 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 09-SEP-21 Starting backup at 09-SEP-21 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=17 RECID=37 STAMP=1082768593 channel ORA_DISK_1: starting piece 1 at 09-SEP-21 channel ORA_DISK_1: finished piece 1 at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/1n08jf6h_1_1 tag=TAG20210909T010313 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 09-SEP-21 Starting Control File and SPFILE Autobackup at 09-SEP-21 piece handle=/u01/app/oracle/oradata/ORCL/backup/c-1608118455-20210909-01 comment=NONE Finished Control File and SPFILE Autobackup at 09-SEP-21
RMAN> list backup of database summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 1 B F A DISK 11-AUG-21 1 1 NO TAG20210811T222830 4 B F A DISK 11-AUG-21 1 1 NO TAG20210811T223126 7 B F A DISK 11-AUG-21 1 1 NO TAG20210811T223345 10 B F A DISK 11-AUG-21 1 1 NO TAG20210811T224016 12 B F A DISK 11-AUG-21 1 1 NO TAG20210811T224942 28 B F A DISK 12-AUG-21 1 1 NO TAG20210812T223307 30 B 0 A DISK 12-AUG-21 1 1 NO TAG20210812T224824 32 B 1 A DISK 12-AUG-21 1 1 NO TAG20210812T225022 34 B 1 A DISK 12-AUG-21 1 1 NO TAG20210812T225145 36 B F A DISK 25-AUG-21 1 1 NO TAG20210825T220051 40 B F A DISK 03-SEP-21 1 1 NO TAG20210903T044703 47 B 0 A DISK 09-SEP-21 1 1 NO TAG20210909T004510 52 B 1 A DISK 09-SEP-21 1 1 NO TAG20210909T010237
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORCL/system01.dbf /u01/app/oracle/oradata/data02.dbf /u01/app/oracle/oradata/ORCL/sysaux01.dbf /u01/app/oracle/oradata/ORCL/undotbs01.dbf /u01/app/oracle/oradata/data01.dbf /u01/app/oracle/oradata/ORCL/users01.dbf
Remove datafiles for testing purposes
[oratest@oracle ~]$ cd /u01/app/oracle/oradata/ORCL/ [oratest@oracle ORCL]$ ls -l total 3099872 drwxrwxr-x. 2 oratest oratest 4096 Sep 9 01:03 backup -rw-rw----. 1 oratest oratest 10600448 Sep 9 01:08 control01.ctl -rw-rw----. 1 oratest oratest 10600448 Sep 9 01:08 control02.ctl -rw-r-----. 1 oratest oratest 209715712 Sep 9 01:00 redo01.log -rw-r-----. 1 oratest oratest 209715712 Sep 9 01:03 redo02.log -rw-r-----. 1 oratest oratest 209715712 Sep 9 01:08 redo03.log -rw-r-----. 1 oratest oratest 1069555712 Sep 9 01:03 sysaux01.dbf -rw-r-----. 1 oratest oratest 975183872 Sep 9 01:03 system01.dbf -rw-r-----. 1 oratest oratest 135274496 Sep 9 00:15 temp01.dbf -rw-r-----. 1 oratest oratest 346038272 Sep 9 01:03 undotbs01.dbf -rw-r-----. 1 oratest oratest 5251072 Sep 9 01:03 users01.dbf [oratest@oracle ORCL]$ rm -rf *
Kill the DB instance, if running. You can do shut abort or kill pmon at OS level
[oratest@oracle ~]$ ps -ef | grep pmon ratest 3584 1 0 Sep08 ? 00:00:00 ora_pmon_test oratest 5868 1 0 00:30 ? 00:00:00 ora_pmon_orcl oratest 6951 4579 0 01:09 pts/0 00:00:00 grep --color=auto pmon [oratest@oracle ~]$ kill -9 5868 [oratest@oracle ~]$ ps -ef |grep pmon oratest 3584 1 0 Sep08 ? 00:00:00 ora_pmon_test oratest 6994 4579 0 01:11 pts/0 00:00:00 grep --color=auto pmon
Start the DB instance and take it to the Mount stage.
[oracle@oracle ~]$ export ORACLE_SID=orcl [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:47:02 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 1728050048 bytes Fixed Size 8897408 bytes Variable Size 436207616 bytes Database Buffers 1275068416 bytes Redo Buffers 7876608 bytes Database mounted. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
Connect to RMAN and recover the Database
[oracle@oracle ~]$ export ORACLE_SID=ORCL [oratest@oracle ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 21:19:59 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1608118455, not open)
execute the run command mention the level 0 and level 1 TAG
RMAN> run
{
RESTORE DATABASE from tag TAG20210909T004510;
RECOVER DATABASE from tag TAG20210909T010237;
RECOVER DATABASE;
sql 'ALTER DATABASE OPEN';
}
Starting restore at 08-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd0_TAG20210908T043841_jmhw7s4x_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd0_TAG20210908T043841_jmhw7s4x_.bkp tag=TAG20210908T043841
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 08-SEP-21
Starting recover at 08-SEP-21
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/ORCL/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCL/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCL/undo01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd1_TAG20210908T044433_jmhwlskb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd1_TAG20210908T044433_jmhwlskb_.bkp tag=TAG20210908T044433
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-SEP-21
Starting recover at 08-SEP-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-SEP-21
sql statement: ALTER DATABASE OPEN
RMAN> exit
Recovery Manager complete.
[oracle@oracle ~]$ cd /u01/app/oracle/oradata/ORCL [oracle@oracle ]$ ls -l total 3099872 -rw-rw----. 1 oratest oratest 10600448 Sep 9 01:08 control01.ctl -rw-rw----. 1 oratest oratest 10600448 Sep 9 01:08 control02.ctl -rw-r-----. 1 oratest oratest 209715712 Sep 9 01:00 redo01.log -rw-r-----. 1 oratest oratest 209715712 Sep 9 01:03 redo02.log -rw-r-----. 1 oratest oratest 209715712 Sep 9 01:08 redo03.log -rw-r-----. 1 oratest oratest 1069555712 Sep 9 01:03 sysaux01.dbf -rw-r-----. 1 oratest oratest 975183872 Sep 9 01:03 system01.dbf -rw-r-----. 1 oratest oratest 135274496 Sep 9 00:15 temp01.dbf -rw-r-----. 1 oratest oratest 346038272 Sep 9 01:03 undotbs01.dbf -rw-r-----. 1 oratest oratest 5251072 Sep 9 01:03 users01.dbf
[oracle@oracle ~]$ export ORACLE_SID=ORCL [oracle@oracle ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:50:32 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
Connect the user and check table rows are recovered, it can be done in level 1 backup
SQL> conn india/india
Connected.
SQL> select * from test;
NO NAME
------ -----
1 one
2 Two
3 Three
4 Four
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
One thought on “LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN”