RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE

 

Below error i got while taking RMAN backup:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/12/2021 03:29:07
RMAN-06183: datafile or datafile copy +ORA_DATA/wwibetest_bosqeumcsdb/datafile/undotbs1.779.1077535587 (file number 3) larger than MAXSETSIZE

Finding and solution:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 30 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/wwi/wwdb/db/oracle/product/11.2.0.4/db_1/dbs/snapcf_WWIBEMCS.f’; # default

RMAN> CONFIGURE MAXSETSIZE TO 35 G;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 30 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 35 G;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 35 G;

 

Now I ran the RMAN backup it went successful.

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

 

RMAN Database Restore ASM

RMAN Database Restore ASM

Description:
In this blog, we are going to see take RMAN backup in asm storage and restore the database to another server.

RMAN Restore ASM:
Take the backup using RMAN in source host copy the backup into destination host, using that backup files to restore the database.

Overall Steps:

Source server:
1. Backup Database using RMAN
2. Create pfile from spfile
3. Copy the backup files to the destination server

Destination server:
1. Edit pfile
2. Create Required Folders in destination server
3. Add the database entry in oratab
4. Startup using pfile Nomount stage
5. Restore control file
6. Mount database
7. Catalog backup pieces
8. Restore and recover the database
9. Change dbname/DBID uisng NID
10. verify the database name and id.

Source Server:

Step 1: Take backup using RMAN:

Create backup directory:

Backup script:
run {
allocate channel t1 type disk;
allocate channel t1 type disk;
allocate channel t1 type disk;
backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
release channel t1;
}
sql ‘alter system archive log current’;
run {
allocate channel a1 type disk;
backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
release channel a1;
}
run {
allocate channel c1 type disk;
backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
release channel c1;
}
exit

Connect RMAN and execute the script:

[oracle@asm ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:01:54 2022
Version 19.3.0.0.0

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

connected to target database: ORAASM (DBID=1977555372)

RMAN> @backup.rcv

RMAN> run {
2> allocate channel t1 type disk;
3> allocate channel t2 type disk;
4> allocate channel t3 type disk;
5> backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
6> release channel t1;
7> release channel t2;
8> release channel t3;
9> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=78 device type=DISK

allocated channel: t2
channel t2: SID=89 device type=DISK

allocated channel: t3
channel t3: SID=88 device type=DISK

Starting backup at 21-JAN-22
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORAASM/DATAFILE/system.257.1092813819
channel t1: starting piece 1 at 21-JAN-22
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORAASM/DATAFILE/sysaux.258.1092813853
input datafile file number=00014 name=+DATA/ORAASM/DATAFILE/tblspace.281.1093044531
input datafile file number=00007 name=+DATA/ORAASM/DATAFILE/users.260.1092813869
channel t2: starting piece 1 at 21-JAN-22
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869
input datafile file number=00015 name=+DATA/ORAASM/DATAFILE/test.282.1094538795
input datafile file number=00013 name=+DATA/ORAASM/DATAFILE/tblspace.280.1093044407
channel t3: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_080jqqo1_8 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:16
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375
input datafile file number=00011 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_060jqqns_6 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:01:10
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_070jqqns_7 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:01:13
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375
input datafile file number=00012 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459
channel t2: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_090jqqop_9 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:49
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0a0jqqq2_10 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:12
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0d0jqqqg_13 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:26
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0b0jqqq9_11 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:27
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0c0jqqqf_12 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:26
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544235.283.1094544237 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: t1

released channel: t2

released channel: t3

RMAN> sql ‘alter system archive log current’;
sql statement: alter system archive log current

RMAN> run {
2> allocate channel a1 type disk;
3> backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
4> release channel a1;
5> }
allocated channel: a1
channel a1: SID=78 device type=DISK

Starting backup at 21-JAN-22
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=1 STAMP=1094544242
input archived log thread=1 sequence=17 RECID=2 STAMP=1094544243
channel a1: starting piece 1 at 21-JAN-22
channel a1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/arch_ORAASM_0f0jqqrk_15 tag=TAG20220121T080403 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544247.289.1094544249 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: a1

RMAN> run {
2> allocate channel c1 type disk;
3> backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
4> release channel c1;
5> }
allocated channel: c1
channel c1: SID=78 device type=DISK

Starting backup at 21-JAN-22
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 21-JAN-22
channel c1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/Control_ORAASM_0h0jqqrs_17 tag=TAG20220121T080412 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544256.290.1094544257 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: c1

RMAN> exit

Recovery Manager complete.

Check the backup file location:

Step 2: Create pfile from spfile

create pfile=’/home/oracle/initasmora.ora’ from spfile;
Step 3: Copy the backup file into destination server

[oracle@asm backup]$ scp * [email protected]:/u01/backup/

Destination server:

Step 1: Edit pfile
Change DB_UNIQUE_NAME in the pfile:

*.audit_file_dest=’/u01/app/oracle/admin/oraasm/adump’
*.audit_trail=’db’
*.compatible=’19.0.0′
*.control_files=’+DATA/ASMORA/CONTROLFILE/current.262.1092813933′,’+DATA/ASMORA/CONTROLFILE/current.261.1092813933′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_name=’oraasm’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=12732m
*.db_unique_name=’asmora’                —–>change db unique name
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraasmXDB)’
*.enable_pluggable_database=true
*.local_listener=’LISTENER_ORAASM’
*.open_cursors=300
*.pga_aggregate_target=629m
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1886m
*.undo_tablespace=’UNDOTBS1′
[oracle@asm dbs]$

Step 2: Create Required Folders

Create required folders in the destination server
[oracle@asmnew ~]$ mkdir -p /u01/app/oracle/admin/ASMORA/adump/

Step 3: Add the Database entry in oratab file.

ASMORA:/u01/app/oracle/product/19.0.0/dbhome_1:N

Step 4: startup database using pfile nomount stage

[oracle@asmnew asmora]$ export ORACLE_SID=asmora
[oracle@asmnew asmora]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Jan 21 08:18:06 2022
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount pfile=’/u01/asmora/initasmora.ora’;
ORACLE instance started.

Total System Global Area 1979709696 bytes
Fixed Size 9136384 bytes
Variable Size 452984832 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7639040 bytes

Step 5: restore control file from backup location

[oracle@asmnew asmora]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:22:04 2022
Version 19.3.0.0.0

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

connected to target database: ORAASM (not mounted)

RMAN> restore controlfile from ‘/u01/share/backup/Control_ORAASM_0h0jqqrs_17’;

Starting restore at 21-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/ORAASM/CONTROLFILE/current.262.1092813933
output file name=+DATA/ORAASM/CONTROLFILE/current.261.1092813933
Finished restore at 21-JAN-22

RMAN>

Step 6: Mount the database.

SQL> alter database mount;

Database altered.

SQL> select name, open_mode, db_unique_name from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME

ORAASM MOUNTED asmora

Step 7: Catalog backup files

CATALOG BACKUPPIECE ‘/u01/asmora/arch_ORAASM_0f0jqqrk_15’;
CATALOG BACKUPPIECE ‘/u01/asmora/Control_ORAASM_0h0jqqrs_17’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_030jqo9c_3’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_060jqqns_6’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_070jqqns_7’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_080jqqo1_8’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_090jqqop_9’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0a0jqqq2_10’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0b0jqqq9_11’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0c0jqqqf_12’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0d0jqqqg_13’;

Step 8: Restore and recover database

List backup archivelogs

RMAN> list backup of archivelog all;

recover database using rman

run
{
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
ALLOCATE CHANNEL d2 DEVICE TYPE disk;
set newname for datafile 1 to ‘+DATA’;
set newname for datafile 2 to ‘+DATA’;
set newname for datafile 3 to ‘+DATA’;
set newname for datafile 4 to ‘+DATA’;
set newname for datafile 5 to ‘+DATA’;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_3.268.1092813965”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_2.266.1092813951”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_1.265.1092813949”
to ”+DATA”” ;
SET UNTIL SEQUENCE 12; <— 11+1
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

check logfile and database role

Step 9:change database name and ID

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

add this controlfile entry to pfile

SQL> startup nomount pfile='initasmora.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount;

Database altered.

SQL>

set db name and id:

[oracle@asm asmora]$ . oraenv
ORACLE_SID = [asmora] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@asm asmora]$ nid target=sys dbname=asmora

DBNEWID: Release 19.0.0.0.0 – Production on Fri Jan 21 09:09:50 2022

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

Password:
Connected to database ORAASM (DBID=1977555372)

Connected to server version 19.3.0

Control Files in database:
+DATA/ASMORA/CONTROLFILE/current.262.1092813933
+DATA/ASMORA/CONTROLFILE/current.261.1092813933

Change database ID and database name ORAASM to ASMORA? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1977555372 to 65466533
Changing database name from ORAASM to ASMORA
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – modified
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – modified
Datafile +DATA/ORAASM/DATAFILE/system.257.109281381 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/sysaux.258.109281385 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/undotbs1.259.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.109281441 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/users.260.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.109281545 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.280.109304440 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.281.109304453 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/test.282.109453879 – dbid changed, wrote new name
Datafile +DATA/ORAASM/TEMPFILE/temp.269.109281404 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CB418C2B91B66E053867EA8C0C5A0/TEMPFILE/temp.273.109281445 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/TEMPFILE/temp.278.109281541 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – dbid changed, wrote new name
Instance shut down

Database name changed to ASMORA.
Modify parameter file and generate a new password file before restarting.
Database ID for database ASMORA changed to 65466533.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

[oracle@asm asmora]$

Step 10: check the DB name and id

SQL> alter database open resetlogs;

Database altered.

SQL> select name, open_mode, db_unique_name, dbid from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME DBID

ASMORA READ WRITE asmora 65466533

 

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

Oracle Database Restore Point

ORACLE DATABASE RESTORE POINT

Description :           In this blog, we are going to see the Oracle database restore point and its types with the demo.Normal Restore Point:
  • A normal restore point enables you to flash the database back to a restore point within the time determined by DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
  • A normal restore point can be dropped explicitly.
  • The control files stores name of the restore point and the SCN.
Guaranteed restore point:
  • A guaranteed restore point enables you to flash the database back to the restore point regardless of DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
  • Guaranteed restore point must be dropped explicitly by the user using the DROP RESTORE POINT command.
  • Guaranteed restore point never ages out.
Normal Restore Point Demo:Must DB in archive log mode.SQL> archive log list; Database log mode              Archive Mode Automatic archival               EnabledCheck available restore point: SQL>  select name from v$restore_point; no rows selectedCreate a normal restore point: SQL> create restore point res_test; Restore point created.View the created restore point: SQL> select name from v$restore_point; NAME ——————– RES_TESTAfter the creation of the restore point creates a table. SQL> create table test as select * from dba_users; Table created.Check the RVWR process status.          In normal restore point no background process are started.  [oracle@primary ~]$ ps -ef |grep -i rvwr oracle   16948 14963  0 09:40 pts/2    00:00:00 grep –color=auto -i rvwrFlashback the restore point: SQL> flashback database to restore point res_test; Flashback complete.Guaranteed Restore Point Demo:Create guarantee restore point: SQL> create restore point res_test1 guarantee flashback database; Restore point created.RVWR back ground process can be started automatically. [oracle@primary ~]$  ps -ef |grep -i rvwr oracle   17014     1  0 09:41 ?        00:00:00 ora_rvwr_orcl oracle   17060 14963  0 09:42 pts/2    00:00:00 grep –color=auto -i rvwr [oracle@primary ~]$Check the flashback status: SQL> select flashback_on from v$database; FLASHBACK_ON —————— RESTORE POINT ONLYAutomatically create flashback logs: [oracle@primary ORCL]$ cd flashback/ [oracle@primary flashback]$ ls o1_mf_jtotn2c3_.flb  o1_mf_jtotn8d4_.flbDrop restore point the logs will be removed. SQL> drop restore point res_test1; Restore point dropped. [oracle@primary ~]$ cd /u01/app/oracle/fast_recovery_area/ORCL/flashback [oracle@primary flashback]$ ls [oracle@primary flashback]$Bounce database and Flashback database to restore point: SQL> flashback database to restore point res_test2; Flashback complete.Reset the logs: SQL> alter database open resetlogs; Database altered.SQL>  select count(*) from test1;   COUNT(*)     ———-         45 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

DATABASE INCARNATION  USING RMAN

ORACLE DATABASE INCARNATION USING RMAN

Description:-
          In this blog, we are going to see the Oracle database incarnation using RMAN backup with the demo.

INCARNATION:-
          The current online redo logs are archived, the log sequence number is reset to 1, a new database incarnation is created, and the online redo logs are given a new timestamp and SCN.

Database incarnation falls into the following category:-
Current, Parent, Ancestor, and Sibling:
i) Current Incarnation: The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation: The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation: The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation: Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

Overall steps:-

  1. Backup full database using RMAN
  2. After backup create a table in the database note the SCN number.
  3. Delete and Drop table take SCN number.
  4. Do log switching and check incarnation status.
  5. Bounce the database started in the mount stage.
  6. Connect RMAN and recover the database mention the sequence number
    where the table record was deleted.
  7. After the recovery is completed perform resetlogs to open the database.
  8. Connect a database to check incarnation also check the table can be retrieved.
  9. Shut the database and start the mount stage again.
  10. Reset the database incarnation 2 to retrieve the table records mention the
    scn where the table was created.
  11. Check record can be retrieved.

Step 1: Backup full database:- 

[oracle@test ~]$ . livedb.env
[oracle@test ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043)
RMAN> backup database;
Starting backup at 15-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_system_jmz2qqys_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_undotbs1_jmz2snrh_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_sysaux_jmz2rvhs_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/LIVEDB/datafile/
o1_mf_users_jmz2sovr_.dbf
channel ORA_DISK_1: starting piece 1 at 15-SEP-21
channel ORA_DISK_1: finished piece 1 at 15-SEP-21
piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/
o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp tag=TAG20210915T035943 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 15-SEP-21

Starting Control File and SPFILE Autobackup at 15-SEP-21
piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_15/
o1_mf_s_1083297650_jn28ntoh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-SEP-21
RMAN> exit

Step 2: After backup create a table in the database note SCN number :-

 [oracle@test ~]$ . livedb.env
[oracle@vtest ~]$ sqlplus / as sysdba
SQL> create table sample as select * from all_objects;
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
———–———–
    2146558

Step 3: Delete and Drop table take SCN number:-
Delete the rows in the sample table:
SQL> delete from the sample;
71297 rows deleted.

Get the scn number also,
SQL> select current_scn from v$database;
CURRENT_SCN
———-———–
    2146578

Drop the table structure:
SQL> drop table sample;
Table dropped.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
——————–
    2146601

Step 4: Do log switching and check incarnation status:-

SQL> alter system switch logfile;
System altered.
Check the incarnation using v$database_incarnation:
SQL> select incarnation#, resetlogs_change# from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE#
———— —————–
               1                        1
               2              1920977

Step 5: Bounce the database start in mount stage:-

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 1694495520 bytes
Fixed Size                       8897312 bytes
Variable Size                 402653184 bytes
Database Buffers        1275068416 bytes
Redo Buffers                  7876608 bytes
Database mounted.

Step 6: Connect RMAN and recover the database mention the sequence number where the table record was deleted:-

[oracle@test ~]$ rman target /
connected to target database: LIVEDB (DBID=3038906043, not open)
RMAN> run{
set until scn=2146578;
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 15-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/
LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/
LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/
backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp 
tag=TAG20210915T035943

channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 15-SEP-21

Starting recover at 15-SEP-21
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 15-SEP-21

Step 7: After the recovery is completed perform resetlogs to open the database:-

RMAN> alter database open resetlogs;
Statement processed
RMAN> exit                                                                

Step 8 : Connect database to check incarnation also check the table can be retrieved:-

[oracle@test ~]$ sqlplus / as sysdba
SQL>  select incarnation#, resetlogs_change# from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE#
————          —————–
               1                        1
               2              1920977
               3              2146579
The table structure is retrieved but the record can be deleted from the scn number stage.
SQL>  select * from the sample;
no rows selected

Step 9: Shut the database and start the mount stage again:-

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 1694495520 bytes
Fixed Size                       8897312 bytes
Variable Size                 402653184 bytes
Database Buffers        1275068416 bytes
Redo Buffers                  7876608 bytes
Database mounted.
SQL> exit

Step 10: Reset the database incarnation 2 to retrieve the table records mention the scn where table created:-

RMAN> reset database to incarnation 2;
using target database control file instead of recovery catalog
database reset to incarnation 2

RMAN> run{
set until scn=2146558;
restore database;
recover database;
}
executing command: SET until clause
Starting restore at 15-SEP-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 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/
LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/
LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/
LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/
LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp 
tag=TAG20210915T035943
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 15-SEP-21

Starting recover at 15-SEP-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file 
/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/
o1_mf_1_18_jn28r8l8_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/
2021_09_15/o1_mf_1_18_jn28r8l8_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:02
Finished recover at 15-SEP-21

Perform reset logs method:

RMAN> alter database open resetlogs;
Statement processed
RMAN> exit

Step 11: Check the record can be retrieved from the table:-

[oracle@test ~]$ sqlplus / as sysdba

SQL> select count(1) from sample;

  COUNT(1)
    ———-
     71297
SQL>

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

Restore Tablespace using RMAN

SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
TEST

6 rows selected.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> exit

[oratest@oracle dbs]$ date
Wed Sep  8 22:15:37 IST 2021

Connect RMAN and backup tablespace 

[oratest@oracle dbs]$  rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 22:16:02 2021
Version 19.3.0.0.0

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

connected to target database: TEST (DBID=2378581000)


RMAN> restore tablespace users until time "to_date
('08-SEP-2021 22:15:37','DD-MON-YYYY:HH24:MI:SS')";

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=66 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 00007 to 
/u01/app/oracle/oradata/TEST/users01.dbf
channel ORA_DISK_1: reading from backup piece 
/u01/app/oracle/oradata/TEST/backup/1408j583_1_1
channel ORA_DISK_1: piece handle=
/u01/app/oracle/oradata/TEST/backup/1408j583_1_1 tag=TAG20210908T221323
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-SEP-21

[oracle@oracle ~]$ rman target/

RMAN> restore tablespace users;
Starting restore at 08-SEP-21
using channel ORA_DISK_1
skipping datafile 5; already restored to file 
/u01/app/oracle/oradata/TEST/datafile/users.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 08-SEP-21

RMAN> recover tablespace users;
Starting recover at 08-SEP-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 32 is already on disk as file 
/u01/app/oracle/fast_recovery_area/TEST/archivelog/2021_09_02/o1_mf_1_32_jlzwlxjb_.arc
media recovery complete, elapsed time: 00:00:12
Finished recover at 08-SEP-21
RMAN> exit
Recovery Manager complete.

[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 22:51:07 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> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/TEST/system01.dbf
/u01/app/oracle/oradata/TEST/test03.dbf
/u01/app/oracle/oradata/TEST/sysaux01.dbf
/u01/app/oracle/oradata/TEST/undotbs01.dbf
/u01/app/oracle/oradata/TEST/test01.dbf
/u01/app/oracle/oradata/TEST/users01.dbf

6 rows selected.

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

RESTORE SPFILE USING RMAN

Check Database Running Parameter

[oratest@oracle ~]$ export ORACLE_SID=test
[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 21:25:19 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>  show parameter pfile;

NAME      TYPE        VALUE
------- ------- ------------------------------
spfile  string   /u01/app/oracle/product/19.0.0
                  /dbhome_1/dbs/spfiletest.ora

Connect RMAN and Backup the Spfile:

[oratest@oracle ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 21:26:38 2021
Version 19.3.0.0.0

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

connected to target database: TEST (DBID=2378581000)

RMAN> backup spfile;

Starting backup at 08-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 08-SEP-21
channel ORA_DISK_1: finished piece 1 at 08-SEP-21
piece handle=/u01/app/oracle/oradata/TEST/backup/1208j2hb_1_1 tag=TAG20210908T212707 
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-SEP-21

Starting Control File and SPFILE Autobackup at 08-SEP-21
piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210908-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08-SEP-21

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 21:28: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

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

Move the Spfile to Backup File

[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
Spfiletest.ora  inittest.ora
[oracle@oracle dbs]$ mv spfiletest.ora spfiletest.ora_bkp
[oracle@oracle dbs]$ ls
initorcl.ora   Spfiletest.ora_bkp

[oratest@oracle dbs]$  rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 21:33:14 2021
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> set dbid 2378581000
executing command: SET DBID


start the database in force option with nomount stage

RMAN> startup force nomount;

Oracle instance started

Total System Global Area    1543500144 bytes

Fixed Size                     8896880 bytes
Variable Size                905969664 bytes
Database Buffers             620756992 bytes
Redo Buffers                   7876608 bytes

Restore the spfile from Auto backup Location

RMAN> restore spfile  from autobackup;
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=38 device type=DISK
recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/autobackup/2021_09_05/o1_mf_s_1082499783_jm9xhjjd_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210905
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/autobackup/2021_09_05/o1_mf_s_1082499783_jm9xhjjd_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-SEP-21
Recovery Manager complete.

[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 5 22:32:18 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> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
TEST      MOUNTED

Using alter command open the database

SQL> alter database open;
Database altered.

SQL>  select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE

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

RMAN BACKUP STRATEGY

RMAN backup Full Database

RMAN Backup Tablespace

RMAN Backup Particular Datafile

RMAN Backup Spfile

RMAN Backup Current Control file

RMAN Backup Archive log Until Sequence

RMAN Backup Archive log Between Sequence

RMAN Backup Archive log Between SCN

RMAN Backup Archive log Until SCN

RMAN Backup Database Plus Archive log

RMAN Backup Database Includes A Control file

RMAN Backup Archive log and All Delete Input

RMAN Backup Archive log All and Skip Inaccessible

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

 

 

LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN

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

LEVEL 0 INCREMENTAL 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

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

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

CROSSCHECK BACKUPS Using RMAN

[oratest@oracle ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 22:50:22 2021
Version 19.3.0.0.0

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

connected to target database: TEST (DBID=2378581000)

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0106mhr1_1_1 RECID=1 STAMP=1080772449
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210816-00 RECID=2 STAMP=1080772496
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0306mo87_1_1 RECID=3 STAMP=1080779015
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210817-00 RECID=4 STAMP=1080779016
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/050797sg_1_1 RECID=5 STAMP=1081384849
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210824-00 RECID=6 STAMP=1081384850
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/070798lh_1_1 RECID=7 STAMP=1081385650
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-01 RECID=8 STAMP=1081385651
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/090798lu_1_1 RECID=9 STAMP=1081385662
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-02 RECID=10 STAMP=1081385719
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0b07bnao_1_1 RECID=11 STAMP=1081466201
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-03 RECID=12 STAMP=1081466202
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0d07bnbi_1_1 RECID=13 STAMP=1081466226
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-04 RECID=14 STAMP=1081466242
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0f07e69t_1_1 RECID=15 STAMP=1081547069
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210825-00 RECID=16 STAMP=1081547125
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0h07e6de_1_1 RECID=17 STAMP=1081547183
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210825-01 RECID=18 STAMP=1081547198
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210830-00 RECID=19 STAMP=1081978741
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0m07rmdf_1_1 RECID=20 STAMP=1081989551
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210831-00 RECID=21 STAMP=1081989628
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210831-01 RECID=22 STAMP=1081997052
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210831-02 RECID=23 STAMP=1081997951
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0q07uikl_1_1 RECID=24 STAMP=1082083989
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210901-00 RECID=25 STAMP=1082083991
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0s07ujal_1_1 RECID=26 STAMP=1082084693
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210901-01 RECID=27 STAMP=1082084759
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0u08ggfl_1_1 RECID=28 STAMP=1082671605
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210907-00 RECID=29 STAMP=1082671607
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/1008gis0_1_1 RECID=30 STAMP=1082674049
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210907-01 RECID=31 STAMP=1082674104
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/1208j2hb_1_1 RECID=32 STAMP=1082755628
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210908-00 RECID=33 STAMP=1082755629
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/1408j583_1_1 RECID=34 STAMP=1082758403
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210908-01 RECID=35 STAMP=1082758405
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210908-02 RECID=36 STAMP=1082759210
Crosschecked 36 objects

Delete Expired Backups

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   EXPIRED     DISK        /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0106mhr1_1_1
2       2       1   1   EXPIRED     DISK        /u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210816-00
3       3       1   1   EXPIRED     DISK        /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0306mo87_1_1
4       4       1   1   EXPIRED     DISK        /u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210817-00
5       5       1   1   EXPIRED     DISK        /u01/app/oracle/product/19.0.0/dbhome_1/dbs/050797sg_1_1
6       6       1   1   EXPIRED     DISK        /u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210824-00
7       7       1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/070798lh_1_1
8       8       1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-01
9       9       1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/090798lu_1_1
10      10      1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-02
11      11      1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/0b07bnao_1_1
12      12      1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-03
13      13      1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/0d07bnbi_1_1
14      14      1   1   EXPIRED     DISK        /u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-04

Do you really want to delete the above objects (enter YES or NO)? yes


deleted backup piece
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0106mhr1_1_1 RECID=1 STAMP=1080772449
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210816-00 RECID=2 STAMP=1080772496
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0306mo87_1_1 RECID=3 STAMP=1080779015
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210817-00 RECID=4 STAMP=1080779016
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/050797sg_1_1 RECID=5 STAMP=1081384849
deleted backup piece
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-2378581000-20210824-00 RECID=6 STAMP=1081384850
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/070798lh_1_1 RECID=7 STAMP=1081385650
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-01 RECID=8 STAMP=1081385651
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/090798lu_1_1 RECID=9 STAMP=1081385662
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-02 RECID=10 STAMP=1081385719
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0b07bnao_1_1 RECID=11 STAMP=1081466201
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-03 RECID=12 STAMP=1081466202
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0d07bnbi_1_1 RECID=13 STAMP=1081466226
deleted backup piece
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210824-04 RECID=14 STAMP=1081466242
Deleted 14 EXPIRED objects

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0f07e69t_1_1 RECID=15 STAMP=1081547069
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210825-00 RECID=16 STAMP=1081547125
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0h07e6de_1_1 RECID=17 STAMP=1081547183
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210825-01 RECID=18 STAMP=1081547198
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210830-00 RECID=19 STAMP=1081978741
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0m07rmdf_1_1 RECID=20 STAMP=1081989551
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210831-00 RECID=21 STAMP=1081989628
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210831-01 RECID=22 STAMP=1081997052
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210831-02 RECID=23 STAMP=1081997951
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0q07uikl_1_1 RECID=24 STAMP=1082083989
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210901-00 RECID=25 STAMP=1082083991
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0s07ujal_1_1 RECID=26 STAMP=1082084693
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210901-01 RECID=27 STAMP=1082084759
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/0u08ggfl_1_1 RECID=28 STAMP=1082671605
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210907-00 RECID=29 STAMP=1082671607
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/1008gis0_1_1 RECID=30 STAMP=1082674049
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210907-01 RECID=31 STAMP=1082674104
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/1208j2hb_1_1 RECID=32 STAMP=1082755628
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210908-00 RECID=33 STAMP=1082755629
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/1408j583_1_1 RECID=34 STAMP=1082758403
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210908-01 RECID=35 STAMP=1082758405
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/TEST/backup/c-2378581000-20210908-02 RECID=36 STAMP=1082759210
Crosschecked 22 objects

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