Feb, 2021

DBVERIFY

It is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files.
You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.

Overview:

Verifying the table and tablespaces
Checking the header block for the table
Stimulating a corruption
Recovering the block using RMAN
Verifying using DATABASE BLOCK CORRUPTION

Step 1:

Verifying the TABLESPACE_NAME and its associated SEGMENT_NAME.

SQL> select SEGMENT_NAME,TABLESPACE_NAME from dba_segments where OWNER=’SCOTT’;

SEGMENT_NAME TABLESPACE_NAME
————— ——————————
SALGRADE USERS
DEPT USERS
EMP USERS
PK_DEPT USERS
PK_EMP USERS

Step 2:

By issuing the below command we can check the header block for the table ‘DEPT’

SELECT header_block FROM dba_segments WHERE segment_name=’DEPT’;

SQL> select file_name from dba_data_files;

FILE_NAME

/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_users_j21snyfk_.dbf
/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_undotbs1_j21snxbn_.dbf
/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_system_j21skfkg_.dbf
/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_sysaux_j21sn40n_.dbf

Step 3:

Command to corrupt the block ,

[oracle@oracle19c ~]$ dd of=/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_users_j21snyfk_.dbf bs=8192 conv=notrunc seek=347 << EOF

corruption test
EOF
0+1 records in
0+1 records out
16 bytes (16 B) copied, 4.9494e-05 s, 323 kB/s

Step 4:

To flush the buffer cache we need to provide the below command

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Step 5:

Selecting the corrupted table , but it shows error

Step 6:

Viewing the corrected block in the database

select * from V$DATABASE_BLOCK_CORRUPTION;

Step 7:

Command to verify the corrupted block across the datafile using the below command

dbv file=/u01/app/oracle/oradata/ORACLE19C/datafile/o1_mf_users_j21snyfk_.dbf blocksize=8192

Step 8:

Command to recover the block and datafile using RMAN,
The datafile and corrupted block we can check it in the previous step ‘DBVERIFY-DBV’ command

recover datafile 7 block 347;

Verification’s:

After recovering there are no rows found in database block corruption ,
And after issuing dbverify command ,there we can see the marked corruption is ‘0’

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

19C RMAN CONCEPTS

19c RMAN CONCEPTS:

RECOVER DATAFILE WITHOUT RMAN BACKUP

RECOVER THROUGH RESETLOGS

BLOCK CHANGE TRACKING

RECOVER LOSS OF ALL CONTROL FILE USING AUTO BACKUP

RECOVER LOSS OF ALL ONLINE REDO LOGS

POINT IN TIME RECOVERY

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

RMAN Database Restore ASM

DATABASE INCARNATION USING RMAN

Restore Tablespace using RMAN

RESTORE SPFILE USING RMAN

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

LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN

CROSSCHECK BACKUPS Using RMAN

RESTORE CONTROL FILE USING RMAN

Backup-based Cloning of a database using RMAN

RECOVERY CATALOG DATABASE IN RMAN

RMAN ORA ERRORS

DBVERIFY

END-OF-FILE ERROR

LEVEL 0 INCREMENTAL BACKUP

point-in-time recovery using RMAN in 19c

Recover a loss of all online redo log files

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

Recover Datafile Without Rman Backup in Oracle 19c

Description:-

In this article we are going to see datafile recovery when no backup exists and lost a datafile at the OS level and there are no current backups. we are in archivelog mode. we have all Archivelogs available since the datafile was created initially .We must have RMAN full backup and archivelogs to recover the datafile when no recent backup of datafile exists.

Step 1: New tablespace creation

SQL> startup
ORACLE instance started.
Total System Global Area         3019895280 bytes
Fixed Size                                     8901104 bytes
Variable Size                               738197504 bytes
Database Buffers                       2264924160 bytes
Redo Buffers    7872512 bytes
Database mounted.
Database opened.
SQL> create tablespace inba datafile ‘/u01/app/oracle/oradata/INBAA/inba_01.dbf’ size 10m;

SQL> select file_name from dba_data_files where tablespace_name=’INBA’;

FILE_NAME

/u01/app/oracle/oradata/INBAA/inba_01.dbf

Step 2: Take RMAN whole database backup and plus archivelog

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 14:06:59 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966)

RMAN> backup database plus archivelog;

Starting backup at 31-JAN-21
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 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=7 RECID=7 STAMP=1063046546
input archived log thread=1 sequence=8 RECID=8 STAMP=1063046565
input archived log thread=1 sequence=9 RECID=9 STAMP=1063046590
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv6d6n_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=1063286511
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv75db_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
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=16 STAMP=1063051981
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv78jp_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=11 STAMP=1063048762
input archived log thread=1 sequence=2 RECID=12 STAMP=1063048795
input archived log thread=1 sequence=3 RECID=13 STAMP=1063048814
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv79no_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=22 STAMP=1063289379
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv7bs0_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=14 STAMP=1063049415
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv7cxv_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=4 RECID=15 STAMP=1063049564
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv7f31_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=2 RECID=17 STAMP=1063051985
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T140939_j1dv7g7b_.bkp tag=TAG20210131T140939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JAN-21
Starting backup at 31-JAN-21
using channel ORA_DISK_1
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/INBAA/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/INBAA/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/INBAA/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/INBAA/inba_01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T141015_j1dv7ho6_.bkp tag=TAG20210131T141015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 31-JAN-21
Starting backup at 31-JAN-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=2 RECID=23 STAMP=1063289460
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T141100_j1dv8wxp_.bkp tag=TAG20210131T141100 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JAN-21
Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_31/o1_mf_s_1063289462_j1dv8yop_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21
RMAN>

Step 3: Add a New Datafile to that Tablespace and verify that the new file is now a member of the tablespace.

SQL> alter tablespace inba add datafile ‘/u01/app/oracle/oradata/INBAA/inba_02.dbf’ size 10m;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name like ‘INBA’;

SQL> alter system switch logfile;

System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>

Step 4: Create a ‘emp’ table in that tablespace and perform some DML operation. Also after DML operations switch some logfile.

SQL> create table emp(id number) tablespace inba;

Table created.
SQL> insert into emp values(11);
1 row created.
SQL> insert into emp values(12);
1 row created.
SQL> insert into emp values(13);
1 row created.
SQL> insert into emp values(14);
1 row created.
SQL> insert into emp values(15);
1 row created.
SQL> select * from emp;

Step 5: In the OS Level,remove all files of that “INBA” tablespace including the newly added one (whose backup does not exist).

Step 6:- Try to bring the tablespace offline and we will get error message as follows

SQL> alter tablespace inba offline;

Tablespace altered.

Step 7: Check the status of tablespace and file_id of datafiles

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS
——————————    ———
SYSTEM                ONLINE
SYSAUX    ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS                               ONLINE
INBA                                 OFFLINE

6 rows selected.

SQL> select file_id,file_name from dba_data_files where tablespace_name like ‘INBA’;

FILE_ID FILE_NAME
———- ——————————
5 /u01/app/oracle/oradata/INBAA/inba_01.dbf
2 /u01/app/oracle/oradata/INBAA/inba_02.dbf

Step 8: Check RMAN backup of INBA tablespace

RMAN> list backup of datafile 5;

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

22 Full 1.32G DISK 00:00:37 31-JAN-21
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20210131T141015
Piece Name: /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T141015_j1dv7ho6_.bkp
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name

5 Full 2392081 31-JAN-21 NO /u01/app/oracle/oradata/INBAA/inba_01.dbf

RMAN> list backup of datafile 2;

specification does not match any backup in the repository

RMAN>

Now connect to RMAN and confirm that no backup exists for the newly added datafile FILE_ID 2. Then try to restore the DBWRTBS tablespace, RMAN will create that newly added datafile as a part of the restore process.

Step 9: Tablespace restore

RMAN> restore tablespace inba;

Starting restore at 31-JAN-21
using channel ORA_DISK_1

creating datafile file number=2 name=/u01/app/oracle/oradata/INBAA/inba_02.dbf
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 00005 to /u01/app/oracle/oradata/INBAA/inba_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T141015_j1dv7ho6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T141015_j1dv7ho6_.bkp tag=TAG20210131T141015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 31-JAN-21

RMAN>

Step 10: Recover tablespace

RMAN> recover tablespace inba;

Starting recover at 31-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_2_j1dv8wp0_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_3_j1dvhzjx_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_4_j1dvj2t2_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_5_j1dvj585_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_2_j1dv8wp0_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_3_j1dvhzjx_.arc thread=1 sequence=3
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-JAN-21
RMAN>

Step 11: To Bring the Tablespace online

SQL> alter tablespace inba online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS

SYSTEM    ONLINE
SYSAUX            ONLINE
UNDOTBS1 ONLINE
TEMP               ONLINE
USERS     ONLINE
INBA              ONLINE

6 rows selected.

SQL> select * from emp;

Step 12: After the tablespace recovery to take a Full Database Backup.

RMAN> backup database plus archivelog;


Starting backup at 31-JAN-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=7 RECID=7 STAMP=1063046546
input archived log thread=1 sequence=8 RECID=8 STAMP=1063046565
input archived log thread=1 sequence=9 RECID=9 STAMP=1063046590
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dws2lk_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=1063286511
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dwsvv2_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
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=22 STAMP=1063289379
input archived log thread=1 sequence=2 RECID=23 STAMP=1063289460
input archived log thread=1 sequence=3 RECID=24 STAMP=1063289687
input archived log thread=1 sequence=4 RECID=25 STAMP=1063289690
input archived log thread=1 sequence=5 RECID=26 STAMP=1063289693
input archived log thread=1 sequence=6 RECID=27 STAMP=1063291002
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dwsz03_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
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=16 STAMP=1063051981
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dwt03o_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=11 STAMP=1063048762
input archived log thread=1 sequence=2 RECID=12 STAMP=1063048795
input archived log thread=1 sequence=3 RECID=13 STAMP=1063048814
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dwt18o_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=14 STAMP=1063049415
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dwt2dh_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=4 RECID=15 STAMP=1063049564
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dwt3k2_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=2 RECID=17 STAMP=1063051985
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143642_j1dwt4nt_.bkp tag=TAG20210131T143642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JAN-21

Starting backup at 31-JAN-21
using channel ORA_DISK_1
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/INBAA/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/INBAA/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/INBAA/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/INBAA/inba_02.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/INBAA/inba_01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T143717_j1dwt60r_.bkp tag=TAG20210131T143717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 31-JAN-21
Starting backup at 31-JAN-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=7 RECID=28 STAMP=1063291103
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_annnn_TAG20210131T143823_j1dww7b5_.bkp tag=TAG20210131T143823 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JAN-21
Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_31/o1_mf_s_1063291104_j1dwwbyt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21

RMAN>

Step 13: Now we have an complete RMAN backups which we newly added.

RMAN> list backup of datafile 5;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Full 1.32G DISK 00:00:37 31-JAN-21
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20210131T141015
Piece Name: /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T141015_j1dv7ho6_.bkp
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2392081 31-JAN-21 NO /u01/app/oracle/oradata/INBAA/inba_01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
35 Full 1.33G DISK 00:00:57 31-JAN-21
BP Key: 35 Status: AVAILABLE Compressed: NO Tag: TAG20210131T143717
Piece Name: /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T143717_j1dwt60r_.bkp
List of Datafiles in backup set 35
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2394449 31-JAN-21 NO /u01/app/oracle/oradata/INBAA/inba_01.dbf

RMAN> list backup of datafile 2;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
35 Full 1.33G DISK 00:00:57 31-JAN-21
BP Key: 35 Status: AVAILABLE Compressed: NO Tag: TAG20210131T143717
Piece Name: /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T143717_j1dwt60r_.bkp
List of Datafiles in backup set 35
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
2 Full 2394449 31-JAN-21 NO /u01/app/oracle/oradata/INBAA/inba_02.dbf

RMAN>

RMAN recovery through RESETLOGS

Description:-

In this article we are going to see RMAN recovery through RESETLOGS.

What does a RESETLOGS do?

Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1.
Creates the online redo log files if they do not currently exist.
Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

The Recovery through Resetlogs feature provides the following benefits:

There is no need to perform a full backup after an incomplete recovery.There is no need to recreate a new standby database after a failover operation.
You can take incremental backups based on full backups of a previous incarnation when you use RMAN.
Block media recovery can restore backups from parent incarnation backups and recover the corrupted blocks through a RESETLOGS operation.
You can use the newly generated logs with an earlier incarnation of the database.

Step 1:- Check the log_archive format as ‘%r’

SQL> show parameter log_archive_format

Note : To perform recovery through RESETLOGS you must have all archived logs generated after the most recent backup. The database would not start if you remove the %r from the log archive format specification.

Oracle 10g introduces a new format specification for archived log files. This new format avoids overwriting archived redo log files with the same sequence number across incarnations.The format specification of the log_archive_format string “%”r represents the resetlogs id. It will ensure that a unique name is constructed for the archived redo log file during RMAN restore and as well as restoring via SQL*plus auto recovery mode.

What is an incarnation?

A database incarnation is created whenever you open the database with the RESETLOGS option.
The Current incarnation is the one in which the database is running at present
The incarnation from which the current incarnation branched after a ALTER DATABASE OPEN RESETLOGS was performed is called the Parent incarnation.
If you want to go back to some SCN which is not part of the Current database incarnation, we need to use the RESET DATABASE TO INCARNATION
command as shown in the example below

Step 2:-Let us check what the current incarnation of the database

[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 18:39:21 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966)
RMAN> list incarnation of database;

using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

1 1 INBAA 936024966 PARENT 1 17-APR-19
2 2 INBAA 936024966 PARENT 1920977 27-JAN-21
3 3 INBAA 936024966 PARENT 2337727 28-JAN-21
4 4 INBAA 936024966 PARENT 2337728 28-JAN-21
5 5 INBAA 936024966 PARENT 2341054 28-JAN-21
6 6 INBAA 936024966 PARENT 2345101 28-JAN-21
7 7 INBAA 936024966 PARENT 2388115 31-JAN-21
8 8 INBAA 936024966 ORPHAN 2399034 31-JAN-21
9 9 INBAA 936024966 PARENT 2399034 31-JAN-21
10 10 INBAA 936024966 CURRENT 2401223 31-JAN-21

RMAN>

Step 3:- Note the CURRENT_SCN of the database.

SQL> select current_scn from v$database;

CURRENT_SCN

2406389

Step 4:- Now, let us make a change in the database which we will then try to undo by restoring and recovering the database to a point in time before the media failure or as in this case, a wrong transaction has happened.

Simulate the wrong DML execution,

SQL> conn inba/inba
Connected.

SQL> create table student(id number);

Table created.
SQL> begin
for i in 1 .. 100000 loop
insert into student values(1);
end loop;
end;
/

SQL> select count(*) from student;

COUNT(*)

100000
SQL> delete from student where rownum < 1001;

1000 rows deleted.
SQL> commit;

Commit complete.
SQL> select count(*) from student;

COUNT(*)

99000
SQL> conn / as sysdba
Connected.

Step 5:- Shutdown and mount the database

SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL>

Step 6:- Now we will rollback the database to an SCN before the delete operation was performed.

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 18:48:55 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966, not open)

RMAN> run
{
set until scn 2406389;
restore database;
recover database;
}2> 3> 4> 5> 6>

executing command: SET until clause
Starting restore at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/INBAA/inba_02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/INBAA/inba_01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T143717_j1dwt60r_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T143717_j1dwt60r_.bkp tag=TAG20210131T143717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-JAN-21
Starting recover at 31-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_7_j1dww74c_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_8_j1f4jhv9_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_1_j1f6hkwh_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_7_j1dww74c_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_8_j1f4jhv9_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_1_j1f6hkwh_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:07
Finished recover at 31-JAN-21
RMAN>

Step 7:- After open resetlogs,check the table count.
We can now see that the deleted rows have been recovered and the number of rows in the table is now the same as before the delete operation was performed.

RMAN> alter database open resetlogs;

SQL> select count(*) from inba.student;

COUNT(*)

100000

Step 8:- Now let us check what the incarnation of the database,

RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

Step 9:- Reset incarnation of database.

RMAN> reset database to incarnation 10;

Step 10:- Set the SCN number of incarnation 10 and try to restore & recover the database

RMAN> run
{
set until scn 2406389;
restore database;
recover database;
}2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/INBAA/inba_02.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/INBAA/inba_01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T143717_j1dwt60r_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T143717_j1dwt60r_.bkp tag=TAG20210131T143717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 31-JAN-21
Starting recover at 31-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_7_j1dww74c_.arc
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_8_j1f4jhv9_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_1_j1f6hkwh_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_7_j1dww74c_.arc thread=1 sequence=7
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_8_j1f4jhv9_.arc thread=1 sequence=8
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_1_j1f6hkwh_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_31/o1_mf_1_1_j1fcq16s_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:08
Finished recover at 31-JAN-21
RMAN>

Step 11:- Open the database using resetlogs option

Now we open the database with the ALTER DATABASE OPEN RESETLOGS command and see that a new incarnation key (12) has been allocated to the database as the CURRENT incarnation.

SQL> alter database open resetlogs;

Database altered.

RMAN> list incarnation of database;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

SQL> select count(*) from inba.student;

Block change Tracking (BCT) in Oracle 19c – CTWR

Description:-

In this article we are going to see use of Oracle Block Change Tracking concept in 19c database.

Oracle 10g introduced the Block change tracking (BCT) feature which is used to Speed up Rman Incremental Backup.
After Enabling this feature records the modified since last backup and stores the log of it in a block change tracking file using the CTWR (Change Tracking Writer) process.
Records the modified since last backup and stores the log of it in a block change tracking file.
Block change tracking feature is not reading the entire database blocks it just directly reads only changed blocks results in block change tracking file.
During backups Rman uses the log file to identify the specific blocks that must be backed up.
It improves the Rman performance as it is not scan whole datafiles instead of read the changed blocks and take backups .

Step 1: Enable Block Change Tracking and Check the status.

SQL> alter database enable block change tracking using file ‘/u01/clone/datafile/blockfile.log’;

SQL> Select filename,status from V$block_change_tracking;

Step 2: To check the change tracking writer(ctwr) process started or not.

SQL> select sid,program,status from v$session where program like ‘%CTWR%’;

Step 3: Disable block change Tracking.

SQL> alter database disable block change tracking;

Database altered.

SQL> select sid,program,status from v$session where program like ‘%CTWR%’;

no rows selected

Resolve ORA-00210,ORA-00202,ORA-27041 and Restore A Loss Of Controlfile Using Autobackup in Oracle 19c

Description

In this article we are going to see how to restore a loss of controlfile using autobackup.

Step 1:- Check the RMAN configuration and controlfile autobackup feature is ON.

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 12:56:58 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name INBAA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/19.0.0/dbs/snapcf_inbaa.f’; # default

RMAN>

Step 2:- Simulate a failure to remove the controlfile when the database is running.

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 31 12:55:02 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> select open_mode,name from v$database;

OPEN_MODE NAME

READ WRITE INBAA

SQL> select name from v$controlfile;

SQL> !
[oracle@localhost scripts]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/oradata/INBAA/
[oracle@localhost INBAA]$ ls
control01.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@localhost INBAA]$ rm -rf control01.ctl
[oracle@localhost INBAA]$ cd
[oracle@localhost ~]$ cd /u01/app/oracle/recovery_area/INBAA/
[oracle@localhost INBAA]$ ls
archivelog autobackup backupset control02.ctl onlinelog
[oracle@localhost INBAA]$ rm -rf control02.ctl

[oracle@localhost INBAA]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sun Jan 31 13:09:30 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.10.0.0.0

SQL>
SQL> select tablespace_name from dba_data_files;
select tablespace_name from dba_data_files
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/oradata/INBAA/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> select status from v$instance;

STATUS

OPEN
SQL>
SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/oradata/INBAA/control01.ctl’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort
ORACLE instance shut down.
SQL>

Step 3:- Keep the database in NOMOUNT stage and restore the controlfile

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
SQL>
Step 4:- Since we are not using a RMAN catalog we need to set the DBID

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Sun Jan 31 13:16:07 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (not mounted)

RMAN> set dbid=936024966;

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 31-JAN-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/recovery_area
database name (or database unique name) used for search: INBAA
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210131
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210130
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210129
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210128
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/INBAA/control01.ctl
output file name=/u01/app/oracle/recovery_area/INBAA/control02.ctl
Finished restore at 31-JAN-21
RMAN>

Step 5:- Mount and recover the database

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed
RMAN> 
RMAN> recover database;

Starting recover at 31-JAN-21
Starting implicit crosscheck backup at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 31-JAN-21
Starting implicit crosscheck copy at 31-JAN-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-JAN-21
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/recovery_area/INBAA/autobackup/2021_01_28/o1_mf_s_1063052742_j15n3h5b_.bkp
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/INBAA/redo01.log
archived log file name=/u01/app/oracle/oradata/INBAA/redo01.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-JAN-21
RMAN>

Step 7:- Open the database using resetlogs option

RMAN> alter database open resetlogs;

Statement processed

RMAN>

SQL> startup nomount;
ORACLE instance started.
Total System Global Area            3019895280 bytes
Fixed Size                                       8901104 bytes
Variable Size                                 738197504 bytes
Database Buffers                         2264924160 bytes
Redo Buffers                                 7872512 bytes
SQL> select open_mode,name from v$database;

Recover a loss of all online redo log files using RMAN in Oracle 19c

Description:-

In this blog I will explain how to recover a loss of all online redo log files using RMAN

Step 1:- Check the member and status of redo logfiles

[oracle@localhost scripts]$ . ora19c.env
[oracle@localhost scripts]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Thu Jan 28 19:17:52 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

SQL> archive log list
Database log mode                        Archive Mode
Automatic archival Enabled
Archive destination                       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence        1
Next log sequence to archive     3
Current log sequence                  3

SQL> select member from v$logfile;

SQL> select * from v$log;

Step 2: We can simulate this scenario by deleting all the online redo log files at the OS level.

Step 2 : Shutdown and mount the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.

Step 3: Connect to RMAN and recover until the available sequence.

[oracle@localhost scripts]$ . ora19c.env 
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 28 20:21:27 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966, not open)

RMAN> run 
{
set until sequence 3;
restore database;
recover database;
alter database open resetlogs;
}2> 3> 4> 5> 6> 7>

executing command: SET until clause
Starting restore at 28-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T200722_j15m12d9_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T200722_j15m12d9_.bkp tag=TAG20210128T200722
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 28-JAN-21
Starting recover at 28-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15mcnb6_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15mcs72_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15mcnb6_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15mcs72_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-JAN-21
Statement processed
RMAN>

SQL> select name,open_mode from v$database;

Since we have done an incomplete recover with open resetlogs, we should take a fresh
complete backup of the database.

Performing point in time recovery using RMAN in 19c

Description:-

RMAN database point-in-time recovery (DBPITR) restores the database from RMAN backups.
RMAN will be consider all ( required ) backups (full, incremental, transectional) to restore or roll forward to the desire time.
Point in time recovery may be incomplete recovery because it does not use all the available archive logs files or completely recover all changes to your database.
If you want to recover your database to the exact date/time in the past, use RMAN point in time recovery.

Prerequisites :-

Database must be running in archivelog mode.
You must have all the datafile backups available prior to target time to recover.

STEP 1:Check archive log list for the current log sequence number.

SQL> archive log list
Database log mode                           Archive Mode
Automatic archival                            Enabled
Archive destination                           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive          4
Current log sequence                       4

Step 2: Create user for testing and table data for the recovery purpose.

SQL> create user inba identified by inba default tablespace users temporary tablespace temp profile default account unlock;

User created.

SQL> grant connect,resource to inba;

Grant succeeded.
SQL> grant all privileges to inba;

Grant succeeded.
SQL> conn inba/inba
Connected.

SQL> create table product(pro_id number,pro_name varchar(10));

Table created.

SQL> insert into product values(11,’laptop’);

1 row created.

SQL> select * from product;

PRO_ID PRO_NAME

11 laptop
12 tv
13 monitor
14 cpu
15 mouse

SQL> select count(*) from product;

COUNT(*)

STEP 3 : Delete all the records in the table and Check the log sequence with specified time in the v$log.

SQL> conn inba/inba
Connected.
SQL> delete product;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.

SQL> select sequence#,first_change#, to_char(first_time,’HH24:MI:SS’) from v$log order by 3;

SEQUENCE# FIRST_CHANGE# TO_CHAR(

2 2341033 19:19:21
3 2341053 19:19:55
4 2341065 19:20:14

Here you can find the time of log sequence 3 19:19:55 that is the time the data would be available in the database.so we need to go back that time and recover the database using the RMAN backups.

Step 4 : Startup the database at mount stage and connect the rman to recover the database using the log sequence number 3.

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 3019895280 bytes
Fixed Size 8901104 bytes
Variable Size 738197504 bytes
Database Buffers 2264924160 bytes
Redo Buffers 7872512 bytes
Database mounted.
SQL>

[oracle@localhost scripts]$ . ora19c.env 
[oracle@localhost scripts]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 28 19:28:33 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: INBAA (DBID=936024966, not open)

RMAN> run
{
set until sequence=3;
restore database;
recover database;
}2> 3> 4> 5> 6>

executing command: SET until clause
Starting restore at 28-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 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/INBAA/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/INBAA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/INBAA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/INBAA/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T143632_j14znrcx_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_28/o1_mf_nnndf_TAG20210128T143632_j14znrcx_.bkp tag=TAG20210128T143632
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 28-JAN-21
Starting recover at 28-JAN-21
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_9_j15g2fl2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15j71yb_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15j83h9_.arc
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_9_j15g2fl2_.arc thread=1 sequence=9
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15gx9vj_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_1_j15j71yb_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/recovery_area/INBAA/archivelog/2021_01_28/o1_mf_1_2_j15j83h9_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:04
Finished recover at 28-JAN-21

RMAN>

STEP 4: Once the recovery has completed,open the database with resetlogs option and check the data in the table.

SQL> alter database open resetlogs;

Database altered.

SQL>

Step 5:- Check the table data now that the point in time recovery has worked.

SQL> select * from inba.product;

PRO_ID PRO_NAME

11 laptop
12 tv
13 monitor
14 cpu
15 mouse

Category Archives: RMAN

Feb, 2021

DBVERIFY

Overview:

FILE_NAME

19c RMAN CONCEPTS:

Backup-based Cloning of a database using RMAN

FILE_NAME

List of Backup Sets

CURRENT_SCN

COUNT(*)

COUNT(*)

COUNT(*)

STATUS

COUNT(*)