ORA-19660: some files in the backup set could not be verified

Issue:

Standby database build from the Active database duplicate failed with "ORA-19660: some files in the backup set could not be verified"

RMAN Error Message: 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 05/07/2025 09:51:08

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-19660: some files in the backup set could not be verified

ORA-19661: datafile 7 could not be verified

Cause: 

Few datafiles on the Primary/PROD database has block corruption 

Fix:

Fix this corrupted block PROD database and retry the standby build using active database duplicate  

Error Logs and commands output:

1. Standby database build from the Active database duplicate failed with "ORA-19660: some files in the backup set could not be verified"

[oracle@oraclelab3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 09:49:33 2025

Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDRDB.ora';

ORACLE instance started.

Total System Global Area  268434280 bytes

Fixed Size                  8895336 bytes

Variable Size             201326592 bytes

Database Buffers           50331648 bytes

Redo Buffers                7880704 bytes

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab3 dbs]$ rman target sys/Mallik123@DEVDB

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 7 09:49:57 2025

Version 19.3.0.0.0

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

connected to target database: DEVDB (DBID=1101319147)

RMAN> connect auxiliary sys/Mallik123@DRDB

connected to auxiliary database: DEVDB (not mounted)

RMAN> run

{

allocate channel p1 type disk;

allocate channel p2 type disk;

allocate auxiliary channel s1 type disk;

allocate auxiliary channel s2 type disk;

duplicate target database for standby from active database

2> 3> 4> 5> 6> 7> 8> spfile

parameter_value_convert 'DEVDB','DRDB'

set db_name='DEVDB'

set db_unique_name='DRDB'

set db_file_name_convert '/u01/app/oracle/oradata/DEVDB/datafile','/u01/app/oracle/oradata/DRDB/datafile'

set log_file_name_convert='/u01/app/oracle/oradata/DEVDB/onlinelog','/u01/app/oracle/oradata/DRDB/onlinelog','/u01/app/oracle/fast_recovery_ar ea/DEVDB/onlinelog','/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'

;

}9> 10> 11> 12> 13> 14> 15>

using target database control file instead of recovery catalog

allocated channel: p1

channel p1: SID=38 device type=DISK

allocated channel: p2

channel p2: SID=18 device type=DISK

allocated channel: s1

channel s1: SID=21 device type=DISK

allocated channel: s2

channel s2: SID=181 device type=DISK

Starting Duplicate Db at 07-MAY-25

contents of Memory Script:

{

   backup as copy reuse

   passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwDRDB'   ;

   restore clone from service  'DEVDB' spfile to

 '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora';

   sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''";

}

executing Memory Script

Starting backup at 07-MAY-25

Finished backup at 07-MAY-25

Starting restore at 07-MAY-25

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: restoring SPFILE

output file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora

channel s1: restore complete, elapsed time: 00:00:01

Finished restore at 07-MAY-25

sql statement: alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''

contents of Memory Script:

{

   sql clone "alter system set  audit_file_dest =

 ''/u01/app/oracle/admin/DRDB/adump'' comment=

 '''' scope=spfile";

   sql clone "alter system set  control_files =

 ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl''  comment=

 '''' scope=spfile";

   sql clone "alter system set  dispatchers =

 ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment=

 '''' scope=spfile";

   sql clone "alter system set  fal_client =

 ''DRDB'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_name =

 ''DEVDB'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''DRDB'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_file_name_convert =

 ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_file_name_convert =

 ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog '', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/DRDB/adump'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_rec overy_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''DRDB'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''DEVDB'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''DRDB'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile''  comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelo g'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    3690985856 bytes

Fixed Size                     8903040 bytes

Variable Size                721420288 bytes

Database Buffers            2952790016 bytes

Redo Buffers                   7872512 bytes

allocated channel: s1

channel s1: SID=4 device type=DISK

allocated channel: s2

channel s2: SID=20 device type=DISK

contents of Memory Script:

{

   sql clone "alter system set  control_files =

  ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl' ' comment=

 ''Set by RMAN'' scope=spfile";

   restore clone from service  'DEVDB' standby controlfile;

}

executing Memory Script

sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_re covery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 07-MAY-25

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: restoring control file

channel s1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl

output file name=/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl

Finished restore at 07-MAY-25

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf";

   set newname for datafile  7 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf";

   restore

   from  nonsparse   from service

 'DEVDB'   clone database

   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 07-MAY-25

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: specifying datafile(s) to restore from backup set

channel s1: restoring datafile 00001 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf

channel s2: starting datafile backup set restore

channel s2: using network backup set from service DEVDB

channel s2: specifying datafile(s) to restore from backup set

channel s2: restoring datafile 00003 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf

channel s1: restore complete, elapsed time: 00:00:04

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: specifying datafile(s) to restore from backup set

channel s1: restoring datafile 00004 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf

channel s1: restore complete, elapsed time: 00:00:01

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: specifying datafile(s) to restore from backup set

channel s1: restoring datafile 00007 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf

channel s2: restore complete, elapsed time: 00:00:04

restore not complete

released channel: p1

released channel: p2

released channel: s1

released channel: s2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 05/07/2025 09:51:08

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-19660: some files in the backup set could not be verified

ORA-19661: datafile 7 could not be verified

RMAN> exit

Recovery Manager complete.

[oracle@oraclelab3 dbs]$

2. Here on the standby server side datafile number 7 which o1_mf_users_my1qh90h_.dbf not getting restored 

[oracle@oraclelab3 ~]$ ls -l /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf

ls: cannot access /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users_my1qh90h_.dbf: No such file or directory

[oracle@oraclelab3 ~]$ cd /u01/app/oracle/oradata/DRDB/datafile/

[oracle@oraclelab3 datafile]$ ls -l

total 2913304

-rw-r-----. 1 oracle oinstall 1436557312 May  7 09:40 o1_mf_sysaux_n1oqb7bg_.dbf

-rw-r-----. 1 oracle oinstall 1142956032 May  7 09:40 o1_mf_system_n1oqb7b5_.dbf

-rw-r-----. 1 oracle oinstall  403709952 May  7 09:40 o1_mf_undotbs1_n1oqbgg5_.dbf

[oracle@oraclelab3 datafile]$

3. On the production database there is a block corruption on this o1_mf_users_my1qh90h_.dbf

[oracle@oraclelab1 DEVDB]$ cd /u01/app/oracle/oradata/DEVDB/datafile/

[oracle@oraclelab1 datafile]$ ll

total 3042000

-rw-r-----. 1 oracle oinstall 1436557312 May  7 09:50 o1_mf_sysaux_my1qggv3_.dbf

-rw-r-----. 1 oracle oinstall 1142956032 May  7 09:50 o1_mf_system_my1qfcqq_.dbf

-rw-r-----. 1 oracle oinstall  136323072 May  5 22:01 o1_mf_temp_my1qjssl_.tmp

-rw-r-----. 1 oracle oinstall  403709952 May  7 09:50 o1_mf_undotbs1_my1qh7xp_.dbf

-rw-r-----. 1 oracle oinstall    5251072 May  7 09:50 o1_mf_users_my1qh90h_.dbf

4. Verify the block corruption using dbv tool 

[oracle@oraclelab1 datafile]$ dbv FILE=o1_mf_users_my1qh90h_.dbf

DBVERIFY: Release 19.0.0.0.0 - Production on Wed May 7 09:53:06 2025

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

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_my1qh90h_.dbf

Page 354 is marked corrupt

Invalid temporary block relative dba: 0x01c00162 (file 7, block 354)

Bad header found during dbv:

Data in bad block:

 type: 99 format: 7 rdba: 0x69747075

 last change scn: 0x7272.7365.74206e6f seq: 0x74 flg: 0x0a

 spare3: 0x0

 consistency value in tail: 0x86df2303

 check value in block header: 0x8240

 block checksum disabled

DBVERIFY - Verification complete

Total Pages Examined         : 640

Total Pages Processed (Data) : 95

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 34

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 441

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 69

Total Pages Marked Corrupt   : 1 >>>>>>>>>>>>> Here is the corruption

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 4294661 (0.4294661)

[oracle@oraclelab1 datafile]$

5. Verify the block corruption using v$database_block_corruption command inside PROD database

[oracle@oraclelab1 datafile]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 09:53:54 2025

Version 19.17.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID

---------- ---------- ---------- ------------------ --------- ----------

         7        354          1                  0 CORRUPT            0

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

[oracle@oraclelab1 datafile]$

6. We have to fix this corrupted block on the datafile number 7 or users datafile using valid RMAN backups. 

Once this corruption is fixed then the RMAN active database duplicate for standby proceeded further. 

In this case here we have dropped that USERS tablespace including the datafile and created new USERS1 tablespace since we don't had any valid backup to recover this corrupted block. 

SQL> drop tablespace users including contents and datafiles;

drop tablespace users including contents and datafiles

*

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

SQL> create tablespace USERS1;

Tablespace created.

SQL> alter database default tablespace USERS1;

Database altered.

SQL> drop tablespace users including contents and datafiles;

Tablespace dropped.

SQL>

SQL> select NAME from v$tablespace;

NAME

------------------------------

SYSAUX

SYSTEM

UNDOTBS1

TEMP

USERS1

SQL>

7. Reran the Standby database build from the Active database duplicate which completed successfully 

[oracle@oraclelab3 DRDB]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 10:00:41 2025

Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/initDRDB.ora';

ORACLE instance started.

Total System Global Area  268434280 bytes

Fixed Size                  8895336 bytes

Variable Size             201326592 bytes

Database Buffers           50331648 bytes

Redo Buffers                7880704 bytes

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oraclelab3 DRDB]$ cd /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/

[oracle@oraclelab3 dbs]$ ll *DRDB*

-rw-rw----. 1 oracle oinstall 1544 May  7 10:00 hc_DRDB.dat

-rw-r--r--. 1 oracle oinstall   14 May  7 09:24 initDRDB.ora

-rw-r-----. 1 oracle oinstall   24 May  7 09:50 lkDRDB

-rw-r-----. 1 oracle oinstall 2048 May  7 09:50 orapwDRDB

-rw-r--r--. 1 oracle oinstall 1632 May  7 09:51 _rm_dup_DRDB_DRDB.dat

-rw-r-----. 1 oracle oinstall 4608 May  7 09:51 spfileDRDB.ora

[oracle@oraclelab3 dbs]$ rm spfileDRDB.ora _rm_dup_DRDB_DRDB.dat

[oracle@oraclelab3 dbs]$ ll *DRDB*

-rw-rw----. 1 oracle oinstall 1544 May  7 10:00 hc_DRDB.dat

-rw-r--r--. 1 oracle oinstall   14 May  7 09:24 initDRDB.ora

-rw-r-----. 1 oracle oinstall   24 May  7 09:50 lkDRDB

-rw-r-----. 1 oracle oinstall 2048 May  7 09:50 orapwDRDB

[oracle@oraclelab3 dbs]$

[oracle@oraclelab3 dbs]$ rman target sys/Mallik123@DEVDB

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 7 10:01:26 2025

Version 19.3.0.0.0

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

connected to target database: DEVDB (DBID=1101319147)

RMAN> connect auxiliary sys/Mallik123@DRDB

connected to auxiliary database: DEVDB (not mounted)

RMAN> run

2> {

3> allocate channel p1 type disk;

4> allocate channel p2 type disk;

allocate auxiliary channel s1 type disk;

5> 6> allocate auxiliary channel s2 type disk;

7> duplicate target database for standby from active database

spfile

parameter_value_convert 'DEVDB','DRDB'

8> 9> 10> set db_name='DEVDB'

11> set db_unique_name='DRDB'

12> set db_file_name_convert '/u01/app/oracle/oradata/DEVDB/datafile','/u01/app/oracle/oradata/DRDB/datafile'

13> set log_file_name_convert='/u01/app/oracle/oradata/DEVDB/onlinelog','/u01/app/oracle/oradata/DRDB/onlinelog','/u01/app/oracle/fast_recover y_area/DEVDB/onlinelog','/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'

;

}14> 15>

using target database control file instead of recovery catalog

allocated channel: p1

channel p1: SID=35 device type=DISK

allocated channel: p2

channel p2: SID=4 device type=DISK

allocated channel: s1

channel s1: SID=21 device type=DISK

allocated channel: s2

channel s2: SID=181 device type=DISK

Starting Duplicate Db at 07-MAY-25

contents of Memory Script:

{

   backup as copy reuse

   passwordfile auxiliary format  '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/orapwDRDB'   ;

   restore clone from service  'DEVDB' spfile to

 '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora';

   sql clone "alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''";

}

executing Memory Script

Starting backup at 07-MAY-25

Finished backup at 07-MAY-25

Starting restore at 07-MAY-25

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: restoring SPFILE

output file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora

channel s1: restore complete, elapsed time: 00:00:01

Finished restore at 07-MAY-25

sql statement: alter system set spfile= ''/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/spfileDRDB.ora''

contents of Memory Script:

{

   sql clone "alter system set  audit_file_dest =

 ''/u01/app/oracle/admin/DRDB/adump'' comment=

 '''' scope=spfile";

   sql clone "alter system set  control_files =

 ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl''  comment=

 '''' scope=spfile";

   sql clone "alter system set  dispatchers =

 ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment=

 '''' scope=spfile";

   sql clone "alter system set  fal_client =

 ''DRDB'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_name =

 ''DEVDB'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''DRDB'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_file_name_convert =

 ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_file_name_convert =

 ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog '', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/DRDB/adump'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_rec overy_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=DRDBXDB)'' comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''DRDB'' comment= '''' scope=spfile

sql statement: alter system set  db_name =  ''DEVDB'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''DRDB'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/datafile'', ''/u01/app/oracle/oradata/DRDB/datafile''  comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u01/app/oracle/oradata/DEVDB/onlinelog'', ''/u01/app/oracle/oradata/DRDB/onlinelo g'', ''/u01/app/oracle/fast_recovery_area/DEVDB/onlinelog'', ''/u01/app/oracle/fast_recovery_area/DRDB/onlinelog'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    3690985856 bytes

Fixed Size                     8903040 bytes

Variable Size                721420288 bytes

Database Buffers            2952790016 bytes

Redo Buffers                   7872512 bytes

allocated channel: s1

channel s1: SID=4 device type=DISK

allocated channel: s2

channel s2: SID=19 device type=DISK

contents of Memory Script:

{

   sql clone "alter system set  control_files =

  ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl' ' comment=

 ''Set by RMAN'' scope=spfile";

   restore clone from service  'DEVDB' standby controlfile;

}

executing Memory Script

sql statement: alter system set  control_files =   ''/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl'', ''/u01/app/oracle/fast_re covery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 07-MAY-25

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: restoring control file

channel s1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/DRDB/controlfile/o1_mf_my1qjmgk_.ctl

output file name=/u01/app/oracle/fast_recovery_area/DRDB/controlfile/o1_mf_my1qjmh4_.ctl

Finished restore at 07-MAY-25

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/DRDB/datafile/o1_mf_users1_n1orby75_.dbf";

   restore

   from  nonsparse   from service

 'DEVDB'   clone database

   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_temp_my1qjssl_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 07-MAY-25

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: specifying datafile(s) to restore from backup set

channel s1: restoring datafile 00001 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_my1qfcqq_.dbf

channel s2: starting datafile backup set restore

channel s2: using network backup set from service DEVDB

channel s2: specifying datafile(s) to restore from backup set

channel s2: restoring datafile 00003 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_my1qggv3_.dbf

channel s1: restore complete, elapsed time: 00:00:03

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: specifying datafile(s) to restore from backup set

channel s1: restoring datafile 00004 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_my1qh7xp_.dbf

channel s1: restore complete, elapsed time: 00:00:01

channel s1: starting datafile backup set restore

channel s1: using network backup set from service DEVDB

channel s1: specifying datafile(s) to restore from backup set

channel s1: restoring datafile 00005 to /u01/app/oracle/oradata/DRDB/datafile/o1_mf_users1_n1orby75_.dbf

channel s2: restore complete, elapsed time: 00:00:04

channel s1: restore complete, elapsed time: 00:00:01

Finished restore at 07-MAY-25

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=1 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_system_n1orlngp_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_sysaux_n1orlngr_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_undotbs1_n1orlqjl_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=1200477745 file name=/u01/app/oracle/oradata/DRDB/datafile/o1_mf_users1_n1orlrkw_.dbf

Finished Duplicate Db at 07-MAY-25

released channel: p1

released channel: p2

released channel: s1

released channel: s2

RMAN> exit

Recovery Manager complete.

[oracle@oraclelab3 dbs]$

8. Further proceed with the post validation and post standby configuration steps. 

Running SQL and O/S Commands Within RMAN

Running SQL and O/S Commands Within RMAN

Sometimes you may want to run an SQL statement from within RMAN. Use RMAN’s sql command to do this. For example:

RMAN> sql "alter system switch logfile";

If there are single quote marks in your SQL, you need to use two single quote marks as shown in this next example:

RMAN> sql "alter database datafile ''/d0101/ordadta/brdstn/users_01.dbf'' offline";

You can also run O/S commands using a similar technique with the host command:

RMAN> host "ls";

Some SQL commands, such as ALTER DATABASE, are directly supported by RMAN. These can be executed directly from the RMAN command prompt, without using the sql command. For example:

RMAN> alter database mount;

Note that the complete syntax of the SQL ALTER DATABASE command is not supported from within RMAN.

RMAN Status Query!!!

RMAN Status Query!!!

How to get RMAN information about the backup type, status, start time,end time, Size of the backup etc!!!

How to find the backup size?

How to check the RMAN backup status?

How to check the previous RMAN backup status?

#Calculate the RMAN backup size 

SET PAGES 2000 LINES 200

SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD') COMPLETION_TIME, TYPE, ROUND(SUM(BYTES)/1048576) MB, ROUND(SUM(ELAPSED_SECONDS)/60) MIN 

FROM 

(SELECT 

CASE 

WHEN S.BACKUP_TYPE='L' THEN 'ARCHIVELOG' 

WHEN S.CONTROLFILE_INCLUDED='YES' THEN 'CONTROLFILE' 

WHEN S.BACKUP_TYPE='D' AND S.INCREMENTAL_LEVEL=0 THEN 'LEVEL0' 

WHEN S.BACKUP_TYPE='I' AND S.INCREMENTAL_LEVEL=1 THEN 'LEVEL1' 

END TYPE, 

TRUNC(S.COMPLETION_TIME) COMPLETION_TIME, P.BYTES, S.ELAPSED_SECONDS 

FROM V$BACKUP_PIECE P, V$BACKUP_SET S 

WHERE P.STATUS='A' AND P.RECID=S.RECID 

UNION ALL 

SELECT 'DATAFILECOPY' type, TRUNC(COMPLETION_TIME), OUTPUT_BYTES, 0 ELAPSED_SECONDS FROM V$BACKUP_COPY_DETAILS) 

GROUP BY TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD'), TYPE 

ORDER BY 1 ASC,2,3; 

RMAN> LIST BACKUP OF DATABASE SUMMARY;

RMAN> LIST BACKUP TAG LV0BKP;

#Calculate RMAN backup status, size, timing and session key
#RMAN information about the backup type, status, start time,end time, Size of the backup etc.

SET PAGES 2000 LINES 200

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

select SESSION_KEY,

INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'mm/dd/yy HH24:MI:SS') START_TIME,

TO_CHAR(END_TIME,'mm/dd/yy HH24:MI:SS') END_TIME,

ELAPSED_SECONDS/3600 HRS,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

#The following query shows the backup job speed ordered by session key, which the primary key for the RMAN session. The columns in_sec and out_sec columns display the data input and output per second.

SET PAGES 2000 LINES 200

COL OPTIMIZED for a10

COL INPUT_PER_SEC FORMAT a20

COL OUTPUT_PER_SEC FORMAT a20

COL TIME_TAKEN_DISPLAY FORMAT a10

SELECT SESSION_KEY,

INPUT_TYPE,

OPTIMIZED,

COMPRESSION_RATIO,

INPUT_BYTES_PER_SEC_DISPLAY INPUT_PER_SEC,

OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_PER_SEC,

TIME_TAKEN_DISPLAY

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;

#RMAN information about the backup type, status, start time,end time, Size of the backup etc from Recovery Catalog

SET PAGES 2000 LINES 200

COL STATUS FORMAT a9

COL HRS FORMAT 999.99

SELECT DB_NAME,

INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'MM/DD/YY HH24:MI:SS') START_TIME,

TO_CHAR(END_TIME,'MM/DD/YY HH24:MI:SS') END_TIME,

ELAPSED_SECONDS/3600 HRS,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM RC_RMAN_BACKUP_JOB_DETAILS

–WHERE DB_NAME='SBLTPS'

ORDER BY DB_NAME,SESSION_KEY;

Reference

http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmreprt.htm#CHDBIGHG

Log:

====

[oracle@node1 ~]$ . oraenv

ORACLE_SID = [oracle] ? ORA19C

The Oracle base has been set to /u01/app/oracle

[oracle@node1 ~]$

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 15 14:54:33 2023

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> SET PAGES 2000 LINES 200

SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD') COMPLETION_TIME, TYPE, ROUND(SUM(BYTES)/1048576) MB, ROUND(SUM(ELAPSED_SECONDS)/60) MIN

FROM

(SELECT

CASE

WHEN S.BACKUP_TYPE='L' THEN 'ARCHIVELOG'

WHEN S.CONTROLFILE_INCLUDED='YES' THEN 'CONTROLFILE'

WHEN S.BACKUP_TYPE='D' AND S.INCREMENTAL_LEVEL=0 THEN 'LEVEL0'

WHEN S.BACKUP_TYPE='I' AND S.INCREMENTAL_LEVEL=1 THEN 'LEVEL1'

SQL>   2    3    4    5    6    7    8    9  END TYPE,

TRUNC(S.COMPLETION_TIME) COMPLETION_TIME, P.BYTES, S.ELAPSED_SECONDS

FROM V$BACKUP_PIECE P, V$BACKUP_SET S

 10   11   12  WHERE P.STATUS='A' AND P.RECID=S.RECID

UNION ALL

 13   14  SELECT 'DATAFILECOPY' type, TRUNC(COMPLETION_TIME), OUTPUT_BYTES, 0 ELAPSED_SECONDS FROM V$BACKUP_COPY_DETAILS)

 15  GROUP BY TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD'), TYPE

 16  ORDER BY 1 ASC,2,3;

COMPLETION_TIME      TYPE                 MB        MIN

-------------------- ------------ ---------- ----------

2023-AUG-01          DATAFILECOPY        776          0

2023-SEP-12          ARCHIVELOG           33          0

2023-SEP-13          ARCHIVELOG          250          2

2023-SEP-14          ARCHIVELOG          225          2

2023-SEP-14          CONTROLFILE         565          1

2023-SEP-14          DATAFILECOPY       3795          0

2023-SEP-15          ARCHIVELOG          183          1

2023-SEP-15          CONTROLFILE         657          1

8 rows selected.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@node1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 15 14:55:15 2023

Version 19.3.0.0.0

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

connected to target database: ORA19C (DBID=1121712241)

RMAN> LIST BACKUP OF DATABASE SUMMARY;

using target database control file instead of recovery catalog

List of Backups

===============

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- --------------- ------- ------- ---------- ---

42981   B  1  A DISK        14-SEP-23       1       1       NO         MALLIK_BACKUP_TAG

RMAN> LIST BACKUP TAG MALLIK_BACKUP_TAG;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

42981   Incr 1  253.69M    DISK        00:00:08     14-SEP-23

        BP Key: 42991   Status: AVAILABLE  Compressed: NO  Tag: MALLIK_BACKUP_TAG

        Piece Name: /var/rubrik/oracle/f9707ae9-ff5d-4333-8291-2b234a6a4865_backup/c0/fh26bpf3_1_1

  List of Datafiles in backup set 42981

  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- --------- ----------- ------ ----

  1    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_system_j64p3dcg_.dbf

  3    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_sysaux_j64p4hpv_.dbf

  4    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_undotbs1_j64p58v3_.dbf

  7    1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_users_j64p59w2_.dbf

  14   1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_user1_j8r9y84m_.dbf

  16   1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_mallik2_j909fm6p_.dbf

  20   1  Incr 74040947   14-SEP-23              NO    /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_test1_ktlw1tvs_.dbf

RMAN> exit

Recovery Manager complete.

[oracle@node1 ~]$

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 15 14:55:41 2023

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> SET PAGES 2000 LINES 200

SQL> COL STATUS FORMAT a9

COL hrs FORMAT 999.99

select SESSION_KEY,

INPUT_TYPE,

STATUS,

SQL> SQL>   2    3    4  TO_CHAR(START_TIME,'mm/dd/yy HH24:MI:SS') START_TIME,

  5  TO_CHAR(END_TIME,'mm/dd/yy HH24:MI:SS') END_TIME,

ELAPSED_SECONDS/3600 HRS,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

  6  OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;  7    8    9   10   11

SESSION_KEY INPUT_TYPE    STATUS    START_TIME        END_TIME              HRS SUM_BYTES_BACKED_IN_GB SUM_BACKUP_PIECES_IN_GB OUTPUT_DEVICE_TYP

----------- ------------- --------- ----------------- ----------------- ------- ---------------------- ----------------------- -----------------

<Truncated>

     215289 DB INCR       COMPLETED 09/14/23 13:43:46 09/14/23 13:45:05     .02             2.54919434              .333282471 DISK

     215305 DB INCR       COMPLETED 09/14/23 13:57:55 09/14/23 13:59:45     .03             2.74450684              .528594971 DISK

     215326 ARCHIVELOG    COMPLETED 09/14/23 14:08:08 09/14/23 14:08:38     .01             .046971798               .04708147 DISK

     215336 ARCHIVELOG    COMPLETED 09/14/23 14:11:57 09/14/23 14:12:11     .00             .042680264              .042789936 DISK

     215346 ARCHIVELOG    COMPLETED 09/14/23 15:08:08 09/14/23 15:08:31     .01             .047270775              .047380447 DISK

     215356 ARCHIVELOG    COMPLETED 09/14/23 16:08:28 09/14/23 16:08:42     .00             .047048092              .047157764 DISK

     215366 ARCHIVELOG    COMPLETED 09/14/23 17:08:18 09/14/23 17:08:32     .00              .04712677              .047236443 DISK

     215376 ARCHIVELOG    COMPLETED 09/14/23 18:08:44 09/14/23 18:08:59     .00             .047767639              .047877312 DISK

     215386 ARCHIVELOG    COMPLETED 09/14/23 19:08:32 09/14/23 19:08:46     .00              .04718399              .047293663 DISK

     215396 ARCHIVELOG    COMPLETED 09/14/23 20:08:36 09/14/23 20:08:58     .01             .046964645              .047074318 DISK

     215406 ARCHIVELOG    COMPLETED 09/14/23 21:08:48 09/14/23 21:09:03     .00             .047271729              .047381401 DISK

     215416 ARCHIVELOG    COMPLETED 09/14/23 22:09:00 09/14/23 22:09:14     .00              .12175703              .121866703 DISK

     215426 ARCHIVELOG    COMPLETED 09/14/23 23:09:00 09/14/23 23:09:15     .00             .084067345              .084177017 DISK

     215436 ARCHIVELOG    COMPLETED 09/15/23 00:09:46 09/15/23 00:10:17     .01             .047124386              .047234058 DISK

     215446 ARCHIVELOG    COMPLETED 09/15/23 01:09:47 09/15/23 01:10:18     .01             .047052383              .047162056 DISK

     215456 ARCHIVELOG    COMPLETED 09/15/23 02:09:43 09/15/23 02:10:06     .01             .046983242              .047092915 DISK

     215466 ARCHIVELOG    COMPLETED 09/15/23 03:09:44 09/15/23 03:09:59     .00             .047057629              .047167301 DISK

     215476 ARCHIVELOG    COMPLETED 09/15/23 04:09:39 09/15/23 04:09:54     .00             .047075272              .047184944 DISK

     215486 ARCHIVELOG    COMPLETED 09/15/23 05:09:31 09/15/23 05:09:42     .00             .046983242              .047092915 DISK

     215496 ARCHIVELOG    COMPLETED 09/15/23 06:09:47 09/15/23 06:10:02     .00             .048101902              .048211575 DISK

     215506 ARCHIVELOG    COMPLETED 09/15/23 07:09:49 09/15/23 07:10:00     .00             .046898365              .047008038 DISK

     215516 ARCHIVELOG    COMPLETED 09/15/23 08:09:57 09/15/23 08:10:11     .00             .046968937              .047078609 DISK

     215526 ARCHIVELOG    COMPLETED 09/15/23 09:09:39 09/15/23 09:09:45     .00             .047005177              .047114849 DISK

     215536 ARCHIVELOG    COMPLETED 09/15/23 10:09:45 09/15/23 10:09:51     .00             .046883583              .046993256 DISK

     215546 ARCHIVELOG    COMPLETED 09/15/23 11:09:45 09/15/23 11:09:52     .00             .046927452              .047037125 DISK

     215556 ARCHIVELOG    COMPLETED 09/15/23 12:10:14 09/15/23 12:10:37     .01             .047740459              .047850132 DISK

     215566 ARCHIVELOG    COMPLETED 09/15/23 13:10:16 09/15/23 13:10:31     .00             .046950817               .04706049 DISK

     215576 ARCHIVELOG    COMPLETED 09/15/23 14:10:21 09/15/23 14:10:32     .00              .04672718              .046836853 DISK

1209 rows selected.

SQL>

SQL> SET PAGES 2000 LINES 200

COL OPTIMIZED for a10

COL INPUT_PER_SEC FORMAT a20

COL OUTPUT_PER_SEC FORMAT a20

COL TIME_TAKEN_DISPLAY FORMAT a10

SELECT SESSION_KEY,

INPUT_TYPE,

OPTIMIZED,

COMPRESSION_RATIO,

INPUT_BYTES_PER_SEC_DISPLAY INPUT_PER_SEC,

OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_PER_SEC,

TIME_TAKEN_DISPLAY

FROM V$RMAN_BACKUP_JOB_DETAILS

ORDER BY SESSION_KEY;SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9

SESSION_KEY INPUT_TYPE    OPTIMIZED  COMPRESSION_RATIO INPUT_PER_SEC        OUTPUT_PER_SEC       TIME_TAKEN

----------- ------------- ---------- ----------------- -------------------- -------------------- ----------

<Truncated>

     215289 DB INCR       NO                7.64875011    33.04M                4.32M            00:01:19

     215305 DB INCR       NO                5.19207898    25.55M                4.92M            00:01:50

     215326 ARCHIVELOG    NO                         1     1.60M                1.61M            00:00:30

     215336 ARCHIVELOG    NO                         1     3.12M                3.13M            00:00:14

     215346 ARCHIVELOG    NO                         1     2.10M                2.11M            00:00:23

     215356 ARCHIVELOG    NO                         1     3.44M                3.45M            00:00:14

     215366 ARCHIVELOG    NO                         1     3.45M                3.46M            00:00:14

     215376 ARCHIVELOG    NO                         1     3.26M                3.27M            00:00:15

     215386 ARCHIVELOG    NO                         1     3.45M                3.46M            00:00:14

     215396 ARCHIVELOG    NO                         1     2.19M                2.19M            00:00:22

     215406 ARCHIVELOG    NO                         1     3.23M                3.23M            00:00:15

     215416 ARCHIVELOG    NO                         1     8.91M                8.91M            00:00:14

     215426 ARCHIVELOG    NO                         1     5.74M                5.75M            00:00:15

     215436 ARCHIVELOG    NO                         1     1.56M                1.56M            00:00:31

     215446 ARCHIVELOG    NO                         1     1.55M                1.56M            00:00:31

     215456 ARCHIVELOG    NO                         1     2.09M                2.10M            00:00:23

     215466 ARCHIVELOG    NO                         1     3.21M                3.22M            00:00:15

     215476 ARCHIVELOG    NO                         1     3.21M                3.22M            00:00:15

     215486 ARCHIVELOG    NO                         1     4.37M                4.38M            00:00:11

     215496 ARCHIVELOG    NO                         1     3.28M                3.29M            00:00:15

     215506 ARCHIVELOG    NO                         1     4.37M                4.38M            00:00:11

     215516 ARCHIVELOG    NO                         1     3.44M                3.44M            00:00:14

     215526 ARCHIVELOG    NO                         1     8.02M                8.04M            00:00:06

     215536 ARCHIVELOG    NO                         1     8.00M                8.02M            00:00:06

     215546 ARCHIVELOG    NO                         1     6.86M                6.88M            00:00:07

     215556 ARCHIVELOG    NO                         1     2.13M                2.13M            00:00:23

     215566 ARCHIVELOG    NO                         1     3.21M                3.21M            00:00:15

     215576 ARCHIVELOG    NO                         1     4.35M                4.36M            00:00:11

1209 rows selected.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@node1 ~]$

Regards,

Mallik

RMAN Level_0 and RMAN level_1 scripts 

Level_0:

export ORACLE_SID=DEVDB

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$PATH:$ORACLE_HOME/bin

rman target / log=/u01/backup/Level_0_`date +%d%b%Y%H%M`.log <<EOF

run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

backup as backupset incremental level 0 database format '/u01/backup/Fullback_%d_%T_%U'

plus archivelog format '/u01/backup/Archive_%T_%U';

backup current controlfile format '/u01/backup/Controlback_%d_%T_%U';

backup spfile format '/u01/backup/spfile_%d_%T_%U';

release channel ch1;

release channel ch2;

}

quit;

EOF

Level_1:

export ORACLE_SID=DEVDB

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$PATH:$ORACLE_HOME/bin

rman target / log=/u01/backup/Level_1_`date +%d%b%Y%H%M`.log <<EOF

run {

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

backup as backupset incremental level 1 database format '/u01/backup/Fullback_%d_%T_%U'

plus archivelog format '/u01/backup/Archive_%T_%U';

backup current controlfile format '/u01/backup/Controlback_%d_%T_%U';

backup spfile format '/u01/backup/spfile_%d_%T_%U';

release channel ch1;

release channel ch2;

}

quit;

EOF

Logs:

Level_0

[oracle@oraclelab1 backup]$ more Level_0_16Feb20230040.log

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 16 00:40:42 2023

Version 19.17.0.0.0

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

connected to target database: DEVDB (DBID=1016989223)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=316 device type=DISK

allocated channel: ch2

channel ch2: SID=38 device type=DISK

Starting backup at 16-FEB-23

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=72 RECID=406 STAMP=1128904041

channel ch1: starting piece 1 at 16-FEB-23

channel ch2: starting archived log backup set

channel ch2: specifying archived log(s) in backup set

input archived log thread=1 sequence=73 RECID=407 STAMP=1128904057

input archived log thread=1 sequence=74 RECID=408 STAMP=1128904220

input archived log thread=1 sequence=75 RECID=409 STAMP=1128904225

input archived log thread=1 sequence=76 RECID=410 STAMP=1128904389

input archived log thread=1 sequence=77 RECID=411 STAMP=1128904407

input archived log thread=1 sequence=78 RECID=412 STAMP=1128904453

channel ch2: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8j1kje4c_275_1_1 tag=TAG20230216T004043 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=79 RECID=413 STAMP=1128904456

input archived log thread=1 sequence=80 RECID=414 STAMP=1128904582

input archived log thread=1 sequence=81 RECID=415 STAMP=1128904599

input archived log thread=1 sequence=82 RECID=416 STAMP=1128904843

channel ch1: starting piece 1 at 16-FEB-23

channel ch2: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8k1kje4c_276_1_1 tag=TAG20230216T004043 comment=NONE

channel ch2: backup set complete, elapsed time: 00:00:01

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8l1kje4d_277_1_1 tag=TAG20230216T004043 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

channel ch1: starting incremental level 0 datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ktbgb34z_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ktbgb356_.dbf

channel ch1: starting piece 1 at 16-FEB-23

channel ch2: starting incremental level 0 datafile backup set

channel ch2: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ktbgb353_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/DEVDB/datafile/users.dbf

channel ch2: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_LEVEL_0_DEVDB_20230216_8m1kje4e_278_1_1 tag=TAG20230216T004046 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:15

channel ch2: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_LEVEL_0_DEVDB_20230216_8n1kje4e_279_1_1 tag=TAG20230216T004046 comment=NONE

channel ch2: backup set complete, elapsed time: 00:00:15

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=83 RECID=417 STAMP=1128904861

channel ch1: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8o1kje4t_280_1_1 tag=TAG20230216T004101 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

channel ch1: starting datafile copy

copying current control file

output file name=/u01/backup/Controlback_DEVDB_20230216_cf_D-DEVDB_id-1016989223_8p1kje4u tag=TAG20230216T004102 RECID=20 STAMP=1128904863

channel ch1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ch1: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/spfile_DEVDB_20230216_8q1kje50_282_1_1 tag=TAG20230216T004104 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting Control File and SPFILE Autobackup at 16-FEB-23

piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2023_02_16/o1_mf_s_1128904865_kytcl9b3_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 16-FEB-23

released channel: ch1

released channel: ch2

RMAN>

Recovery Manager complete.

[oracle@oraclelab1 backup]$

Level_1

[oracle@oraclelab1 backup]$ more Level_1_16Feb20230042.log

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 16 00:42:06 2023

Version 19.17.0.0.0

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

connected to target database: DEVDB (DBID=1016989223)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=316 device type=DISK

allocated channel: ch2

channel ch2: SID=31 device type=DISK

Starting backup at 16-FEB-23

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=72 RECID=406 STAMP=1128904041

channel ch1: starting piece 1 at 16-FEB-23

channel ch2: starting archived log backup set

channel ch2: specifying archived log(s) in backup set

input archived log thread=1 sequence=73 RECID=407 STAMP=1128904057

input archived log thread=1 sequence=74 RECID=408 STAMP=1128904220

input archived log thread=1 sequence=75 RECID=409 STAMP=1128904225

input archived log thread=1 sequence=76 RECID=410 STAMP=1128904389

input archived log thread=1 sequence=77 RECID=411 STAMP=1128904407

input archived log thread=1 sequence=78 RECID=412 STAMP=1128904453

input archived log thread=1 sequence=79 RECID=413 STAMP=1128904456

channel ch2: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8s1kje70_284_1_1 tag=TAG20230216T004208 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=80 RECID=414 STAMP=1128904582

input archived log thread=1 sequence=81 RECID=415 STAMP=1128904599

input archived log thread=1 sequence=82 RECID=416 STAMP=1128904843

input archived log thread=1 sequence=83 RECID=417 STAMP=1128904861

input archived log thread=1 sequence=84 RECID=418 STAMP=1128904928

channel ch1: starting piece 1 at 16-FEB-23

channel ch2: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8t1kje70_285_1_1 tag=TAG20230216T004208 comment=NONE

channel ch2: backup set complete, elapsed time: 00:00:01

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8u1kje71_286_1_1 tag=TAG20230216T004208 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

channel ch1: starting incremental level 1 datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ktbgb34z_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ktbgb356_.dbf

channel ch1: starting piece 1 at 16-FEB-23

channel ch2: starting incremental level 1 datafile backup set

channel ch2: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ktbgb353_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/DEVDB/datafile/users.dbf

channel ch2: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_LEVEL_1_DEVDB_20230216_8v1kje72_287_1_1 tag=TAG20230216T004210 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

channel ch2: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_LEVEL_1_DEVDB_20230216_901kje72_288_1_1 tag=TAG20230216T004210 comment=NONE

channel ch2: backup set complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=85 RECID=419 STAMP=1128904931

channel ch1: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_911kje73_289_1_1 tag=TAG20230216T004211 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

channel ch1: starting datafile copy

copying current control file

output file name=/u01/backup/Controlback_DEVDB_20230216_cf_D-DEVDB_id-1016989223_921kje75 tag=TAG20230216T004212 RECID=21 STAMP=1128904933

channel ch1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting backup at 16-FEB-23

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ch1: starting piece 1 at 16-FEB-23

channel ch1: finished piece 1 at 16-FEB-23

piece handle=/u01/backup/spfile_DEVDB_20230216_931kje76_291_1_1 tag=TAG20230216T004214 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-FEB-23

Starting Control File and SPFILE Autobackup at 16-FEB-23

piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2023_02_16/o1_mf_s_1128904935_kytcnhdt_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 16-FEB-23

released channel: ch1

released channel: ch2

RMAN>

Recovery Manager complete.

[oracle@oraclelab1 backup]$ 

Regards,

Mallik

RMAN Point-In-Time Recovery

RMAN Point-In-Time Recovery

Whenever request from application team to flashback or restore database to point in time these setps will help you.

Questions:

Restore DEVDB database to 18-NOV-2022 22:20:22 IST.

Answer:

Follow the below steps

High Level steps:

1. Check proper backups available or not (If no backups we can not do point in time recovery)

rman target /

RMAN> list backup;

2. Shutdown DB and start DB in mount mode

rman target /

RMAN> shutdown immediate;

RMAN> startup mount;

3. Restore and Recover database to point in time

RMAN> run

{

allocate channel ch1 type disk;

set until time "to_date('2022-11-18:22:20:22', 'yyyy-mm-dd:hh24:mi:ss')";

restore database;

recover database; 

}

4. Open database with resetlogs

RMAN> alter database open resetlogs;

Logs:

[oracle@oraclelab1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 18 22:09:58 2022

Version 19.17.0.0.0

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

connected to target database: DEVDB (DBID=1016989223)

RMAN> run {

allocate channel ch1 device type disk;

crosscheck backup;

crosscheck archivelog all;

backup as backupset database format '/u01/backup/Fullback_%T_%U'

plus archivelog format '/u01/backup/Archive_%T_%U';

backup current controlfile format '/u01/backup/Controlback_%T_%U';

2> 3> release channel ch1;

}4> 5> 6> 7> 8> 9>

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=17 device type=DISK

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221010_1s19ui6l_60_1_1 RECID=52 STAMP=1117735125

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221010_1t19ui6l_61_1_1 RECID=53 STAMP=1117735125

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/RMAN/database/full_backup_DEVDB_20221018_291airdf_73_1_1 RECID=64 STAMP=1118399919

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2e1aldgt_78_1_1 RECID=68 STAMP=1118483997

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2d1aldgt_77_1_1 RECID=69 STAMP=1118483997

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_2g1aldh0_80_1_1 RECID=70 STAMP=1118484000

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_2f1aldh0_79_1_1 RECID=71 STAMP=1118484000

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2h1aldhf_81_1_1 RECID=72 STAMP=1118484015

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Controlback_20221019_2i1aldhh_82_1_1 RECID=73 STAMP=1118484018

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2l1aldqc_85_1_1 RECID=75 STAMP=1118484300

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2k1aldqc_84_1_1 RECID=76 STAMP=1118484300

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_2n1aldqg_87_1_1 RECID=77 STAMP=1118484304

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_2m1aldqg_86_1_1 RECID=78 STAMP=1118484304

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2o1aldqh_88_1_1 RECID=79 STAMP=1118484305

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Controlback_20221019_2p1aldqi_89_1_1 RECID=80 STAMP=1118484307

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2s1alffi_92_1_1 RECID=82 STAMP=1118486002

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2r1alffi_91_1_1 RECID=83 STAMP=1118486002

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_2u1alffl_94_1_1 RECID=84 STAMP=1118486006

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_2t1alffl_93_1_1 RECID=85 STAMP=1118486006

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_2v1alfg5_95_1_1 RECID=86 STAMP=1118486021

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Controlback_20221019_301alfg6_96_1_1 RECID=87 STAMP=1118486023

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_341algkt_100_1_1 RECID=90 STAMP=1118487197

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_331algkt_99_1_1 RECID=91 STAMP=1118487197

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_361algl1_102_1_1 RECID=92 STAMP=1118487201

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221019_351algl1_101_1_1 RECID=93 STAMP=1118487201

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221019_371alglg_103_1_1 RECID=94 STAMP=1118487216

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Controlback_20221019_381alglh_104_1_1 RECID=95 STAMP=1118487218

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221020_3a1ao0kd_106_1_1 RECID=97 STAMP=1118569101

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221020_3b1ao175_107_1_1 RECID=98 STAMP=1118569701

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Fullback_20221020_3c1ao178_108_1_1 RECID=99 STAMP=1118569705

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Archive_20221020_3d1ao17o_109_1_1 RECID=100 STAMP=1118569720

crosschecked backup piece: found to be 'EXPIRED'

backup piece handle=/u01/backup/Controlback_20221020_3e1ao17p_110_1_1 RECID=101 STAMP=1118569722

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_10_20/o1_mf_s_1118569723_ko1m13jj_.bkp RECID=102 STAMP=1118569723

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_11_07/o1_mf_s_1120125661_kpk2j5m7_.bkp RECID=103 STAMP=1120125661

Crosschecked 34 objects

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_195_kqhfbn6k_.arc RECID=187 STAMP=1121119789

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_196_kqhfbohz_.arc RECID=188 STAMP=1121119792

validation succeeded for archived log

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_197_kqhfbon5_.arc RECID=189 STAMP=1121119793

Crosschecked 3 objects

Starting backup at 18-NOV-22

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=195 RECID=187 STAMP=1121119789

input archived log thread=1 sequence=196 RECID=188 STAMP=1121119792

input archived log thread=1 sequence=197 RECID=189 STAMP=1121119793

input archived log thread=1 sequence=198 RECID=190 STAMP=1121119816

channel ch1: starting piece 1 at 18-NOV-22

channel ch1: finished piece 1 at 18-NOV-22

piece handle=/u01/backup/Archive_20221118_3h1d5ri8_113_1_1 tag=TAG20221118T221016 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:03

Finished backup at 18-NOV-22

Starting backup at 18-NOV-22

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_kh3nrlj4_.dbf

input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_kh3nqhcn_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_kh3nscnc_.dbf

input datafile file number=00007 name=/tmp/users.dbf

channel ch1: starting piece 1 at 18-NOV-22

channel ch1: finished piece 1 at 18-NOV-22

piece handle=/u01/backup/Fullback_20221118_3i1d5rib_114_1_1 tag=TAG20221118T221019 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:15

Finished backup at 18-NOV-22

Starting backup at 18-NOV-22

current log archived

channel ch1: starting archived log backup set

channel ch1: specifying archived log(s) in backup set

input archived log thread=1 sequence=199 RECID=191 STAMP=1121119834

channel ch1: starting piece 1 at 18-NOV-22

channel ch1: finished piece 1 at 18-NOV-22

piece handle=/u01/backup/Archive_20221118_3j1d5riq_115_1_1 tag=TAG20221118T221034 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-NOV-22

Starting backup at 18-NOV-22

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

including current control file in backup set

channel ch1: starting piece 1 at 18-NOV-22

channel ch1: finished piece 1 at 18-NOV-22

piece handle=/u01/backup/Controlback_20221118_3k1d5ris_116_1_1 tag=TAG20221118T221036 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:01

Finished backup at 18-NOV-22

Starting Control File and SPFILE Autobackup at 18-NOV-22

piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_11_18/o1_mf_s_1121119838_kqhfd6d5_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 18-NOV-22

released channel: ch1

RMAN> exit

Recovery Manager complete.

You have new mail in /var/spool/mail/oracle

[oracle@oraclelab1 ~]$

[oracle@oraclelab1 ~]$ cd /u01/backup/

[oracle@oraclelab1 backup]$ ll

total 3173388

-rw-r-----. 1 oracle oinstall  560015360 Nov 18 22:10 Archive_20221118_3h1d5ri8_113_1_1

-rw-r-----. 1 oracle oinstall      29184 Nov 18 22:10 Archive_20221118_3j1d5riq_115_1_1

-rw-r-----. 1 oracle oinstall   10715136 Nov 18 22:10 Controlback_20221118_3k1d5ris_116_1_1

-rw-r-----. 1 oracle oinstall 2678784000 Nov 18 22:10 Fullback_20221118_3i1d5rib_114_1_1

[oracle@oraclelab1 backup]$

[oracle@oraclelab1 2022_11_18]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 22:18:10 2022

Version 19.17.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> conn mallik/mallik

Connected.

SQL> create table before_pit_table(SLNO number(2));

Table created.

SQL> 

SQL> insert into before_pit_table values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from before_pit_table;

      SLNO

----------

         1

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> !date

Fri Nov 18 22:20:22 IST 2022

SQL> create table after_pit_table(SLNO number(2));

Table created.

SQL> insert into after_pit_table values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from after_pit_table;

      SLNO

----------

         2

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> !date

Fri Nov 18 22:22:38 IST 2022

SQL>

SQL> conn / as sysdba

Connected.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

You have new mail in /var/spool/mail/oracle

[oracle@oraclelab1 2022_11_18]$

[oracle@oraclelab1 2022_11_18]$ rman TARGET /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 18 22:23:52 2022

Version 19.17.0.0.0

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started

database mounted

Total System Global Area    3690985856 bytes

Fixed Size                     8903040 bytes

Variable Size               1577058304 bytes

Database Buffers            2097152000 bytes

Redo Buffers                   7872512 bytes

RMAN> run

{

allocate channel ch1 type disk;

set until time "to_date('2022-11-18:22:20:22', 'yyyy-mm-dd:hh24:mi:ss')";

restore database;

recover database;

}2> 3> 4> 5> 6> 7>

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=259 device type=DISK

executing command: SET until clause

Starting restore at 18-NOV-22

channel ch1: starting datafile backup set restore

channel ch1: specifying datafile(s) to restore from backup set

channel ch1: restoring datafile 00001 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_kh3nqhcn_.dbf

channel ch1: restoring datafile 00003 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_kh3nrlj4_.dbf

channel ch1: restoring datafile 00004 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_kh3nscnc_.dbf

channel ch1: restoring datafile 00007 to /tmp/users.dbf

channel ch1: reading from backup piece /u01/backup/Fullback_20221118_3i1d5rib_114_1_1

channel ch1: piece handle=/u01/backup/Fullback_20221118_3i1d5rib_114_1_1 tag=TAG20221118T221019

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:15

Finished restore at 18-NOV-22

Starting recover at 18-NOV-22

starting media recovery

archived log for thread 1 with sequence 199 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_199_kqhfd2s3_.arc

archived log for thread 1 with sequence 200 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_200_kqhfog2t_.arc

archived log for thread 1 with sequence 201 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_201_kqhfrq49_.arc

archived log for thread 1 with sequence 202 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_202_kqhfxslo_.arc

archived log for thread 1 with sequence 203 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_203_kqhfxxc3_.arc

archived log for thread 1 with sequence 204 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_204_kqhfy0g4_.arc

archived log for thread 1 with sequence 205 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_205_kqhfy3js_.arc

archived log for thread 1 with sequence 206 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_206_kqhfy6ho_.arc

archived log for thread 1 with sequence 207 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_207_kqhfy9k3_.arc

archived log for thread 1 with sequence 208 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_208_kqhfydhc_.arc

archived log for thread 1 with sequence 209 is already on disk as file /u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_209_kqhg0gq5_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_199_kqhfd2s3_.arc thread=1 sequence=199

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_200_kqhfog2t_.arc thread=1 sequence=200

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_201_kqhfrq49_.arc thread=1 sequence=201

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_202_kqhfxslo_.arc thread=1 sequence=202

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_203_kqhfxxc3_.arc thread=1 sequence=203

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_204_kqhfy0g4_.arc thread=1 sequence=204

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_205_kqhfy3js_.arc thread=1 sequence=205

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_206_kqhfy6ho_.arc thread=1 sequence=206

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_207_kqhfy9k3_.arc thread=1 sequence=207

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_208_kqhfydhc_.arc thread=1 sequence=208

archived log file name=/u01/app/oracle/fast_recovery_area/DEVDB/archivelog/2022_11_18/o1_mf_1_209_kqhg0gq5_.arc thread=1 sequence=209

media recovery complete, elapsed time: 00:00:05

Finished recover at 18-NOV-22

released channel: ch1

RMAN>

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit

Recovery Manager complete.

You have new mail in /var/spool/mail/oracle

[oracle@oraclelab1 2022_11_18]$

[oracle@oraclelab1 2022_11_18]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 22:27:20 2022

Version 19.17.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> conn mallik/mallik

Connected.

SQL> select * from before_pit_table;

      SLNO

----------

         1

SQL> select * from after_pit_table;

select * from after_pit_table

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Regards,

Mallik