create catalog - ORACLEAGENT BLOG

Restore Tablespace using RMAN

SQL> select name from v$tablespace;

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

6 rows selected.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> exit

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

Connect RMAN and backup tablespace

[oratest@oracle dbs]$  rman target/

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

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

connected to target database: TEST (DBID=2378581000)

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

Starting restore at 08-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to 
/u01/app/oracle/oradata/TEST/users01.dbf
channel ORA_DISK_1: reading from backup piece 
/u01/app/oracle/oradata/TEST/backup/1408j583_1_1
channel ORA_DISK_1: piece handle=
/u01/app/oracle/oradata/TEST/backup/1408j583_1_1 tag=TAG20210908T221323
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-SEP-21

[oracle@oracle ~]$ rman target/

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

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

[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 22:51:07 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name from v$datafile;

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

6 rows selected.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

RESTORE SPFILE USING RMAN

Check Database Running Parameter

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

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 21:25:19 2021
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SQL>  show parameter pfile;

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

Connect RMAN and Backup the Spfile:

[oratest@oracle ~]$ rman target /

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

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

connected to target database: TEST (DBID=2378581000)

RMAN> backup spfile;

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

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

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 21:28:32 2021
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

Move the Spfile to Backup File

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

[oratest@oracle dbs]$  rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 21:33:14 2021
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> set dbid 2378581000
executing command: SET DBID


start the database in force option with nomount stage

RMAN> startup force nomount;

Oracle instance started

Total System Global Area    1543500144 bytes

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

Restore the spfile from Auto backup Location

RMAN> restore spfile  from autobackup;
Starting restore at 08-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/autobackup/2021_09_05/o1_mf_s_1082499783_jm9xhjjd_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210905
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/TEST/autobackup/2021_09_05/o1_mf_s_1082499783_jm9xhjjd_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-SEP-21
Recovery Manager complete.

[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 5 22:32:18 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

Using alter command open the database

SQL> alter database open;
Database altered.

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

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

RMAN BACKUP STRATEGY

RMAN backup Full Database

RMAN Backup Tablespace

RMAN Backup Particular Datafile

RMAN Backup Spfile

RMAN Backup Current Control file

RMAN Backup Archive log Until Sequence

RMAN Backup Archive log Between Sequence

RMAN Backup Archive log Between SCN

RMAN Backup Archive log Until SCN

RMAN Backup Database Plus Archive log

RMAN Backup Database Includes A Control file

RMAN Backup Archive log and All Delete Input

RMAN Backup Archive log All and Skip Inaccessible

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN

Connect rman and Take level 0 backup

[oratest@oracle dbs]$  rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 00:42:53 2021
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1608118455)

RMAN> backup incremental level 0 database plus archivelog;


Starting backup at 09-SEP-21
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=471 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=21 STAMP=1082265443
input archived log thread=1 sequence=2 RECID=22 STAMP=1082354497
input archived log thread=1 sequence=3 RECID=23 STAMP=1082412592
input archived log thread=1 sequence=4 RECID=24 STAMP=1082470208
input archived log thread=1 sequence=5 RECID=25 STAMP=1082554248
input archived log thread=1 sequence=6 RECID=26 STAMP=1082640636
input archived log thread=1 sequence=7 RECID=27 STAMP=1082677257
input archived log thread=1 sequence=9 RECID=29 STAMP=1082766943
input archived log thread=1 sequence=10 RECID=30 STAMP=1082766950
input archived log thread=1 sequence=11 RECID=31 STAMP=1082766986
input archived log thread=1 sequence=12 RECID=32 STAMP=1082766988
input archived log thread=1 sequence=13 RECID=33 STAMP=1082766991
input archived log thread=1 sequence=14 RECID=34 STAMP=1082767405
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1f08je1e_1_1 tag=TAG20210909T004325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=9 STAMP=1081792908
input archived log thread=1 sequence=14 RECID=10 STAMP=1081863051
input archived log thread=1 sequence=15 RECID=11 STAMP=1081953019
input archived log thread=1 sequence=16 RECID=12 STAMP=1082066461
input archived log thread=1 sequence=17 RECID=19 STAMP=1082263361
input archived log thread=1 sequence=18 RECID=20 STAMP=1082263367
input archived log thread=1 sequence=19 RECID=18 STAMP=1082263356
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1g08je36_1_1 tag=TAG20210909T004325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47
Finished backup at 09-SEP-21

Starting backup at 09-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/data02.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/data01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1h08je4m_1_1 tag=TAG20210909T004510 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 09-SEP-21

Starting backup at 09-SEP-21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=35 STAMP=1082767577
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1i08je6p_1_1 tag=TAG20210909T004617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-SEP-21

Starting Control File and SPFILE Autobackup at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/c-1608118455-20210909-00 comment=NONE
Finished Control File and SPFILE Autobackup at 09-SEP-21



Check backup summary and note the level 0 backup TAG

RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
--- -- -- - ----------- --------------- ------- ------- ---------- ---
1     B  F  A  DISK   11-AUG-21     1     1       NO  TAG20210811T222830
4     B  F  A  DISK   11-AUG-21     1     1       NO  TAG20210811T223126
7     B  F  A  DISK   11-AUG-21     1     1       NO  TAG20210811T223345
10    B  F  A DISK    11-AUG-21     1     1       NO  TAG20210811T224016
12    B  F  A DISK    11-AUG-21     1     1       NO  TAG20210811T224942

28    B  F  A DISK    12-AUG-21     1     1       NO  TAG20210812T223307
30    B  0  A DISK    12-AUG-21     1     1       NO  TAG20210812T224824
32    B  1  A DISK    12-AUG-21     1     1       NO  TAG20210812T225022
34    B  1  A DISK    12-AUG-21     1     1       NO  TAG20210812T225145
36    B  F  A DISK    25-AUG-21     1     1       NO  TAG20210825T220051
40    B  F  A DISK    03-SEP-21     1     1       NO  TAG20210903T044703
47    B  0  A DISK    09-SEP-21     1     1       NO  TAG20210909T004510


RMAN> exit

Once Level 0 is completed creating a user and table insert the record for the purpose of level 1 Backup

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

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:42:05 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create user india identified by india;

User created.

SQL> grant connect, resource to india;

Grant succeeded.

SQL> alter user india default tablespace users quota unlimited on users;

User altered.

SQL> conn india/india
Connected.

create table test (no number(10),name varchar2(20));

SQL>  insert into test values(1,'one');

1 row created.

SQL> insert into test values(2,'Two');

1 row created.

SQL> insert into test values(3,'Three');

1 row created.

SQL> insert into test values(4,'Four');

1 row created.

SQL> commit;

Commit complete.

Connect RMAN And Take Level 1 Backup

 [oratest@oracle dbs]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 01:00:32 2021
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1608118455)

RMAN> backup incremental level 1 database plus archivelog;


Starting backup at 09-SEP-21
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=21 STAMP=1082265443
input archived log thread=1 sequence=2 RECID=22 STAMP=1082354497
input archived log thread=1 sequence=3 RECID=23 STAMP=1082412592
input archived log thread=1 sequence=4 RECID=24 STAMP=1082470208
input archived log thread=1 sequence=5 RECID=25 STAMP=1082554248
input archived log thread=1 sequence=6 RECID=26 STAMP=1082640636
input archived log thread=1 sequence=7 RECID=27 STAMP=1082677257
input archived log thread=1 sequence=9 RECID=29 STAMP=1082766943
input archived log thread=1 sequence=10 RECID=30 STAMP=1082766950
input archived log thread=1 sequence=11 RECID=31 STAMP=1082766986
input archived log thread=1 sequence=12 RECID=32 STAMP=1082766988
input archived log thread=1 sequence=13 RECID=33 STAMP=1082766991
input archived log thread=1 sequence=14 RECID=34 STAMP=1082767405
input archived log thread=1 sequence=15 RECID=35 STAMP=1082767577
input archived log thread=1 sequence=16 RECID=36 STAMP=1082768454
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1k08jf27_1_1 tag=TAG20210909T010055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=9 STAMP=1081792908
input archived log thread=1 sequence=14 RECID=10 STAMP=1081863051
input archived log thread=1 sequence=15 RECID=11 STAMP=1081953019
input archived log thread=1 sequence=16 RECID=12 STAMP=1082066461
input archived log thread=1 sequence=17 RECID=19 STAMP=1082263361
input archived log thread=1 sequence=18 RECID=20 STAMP=1082263367
input archived log thread=1 sequence=19 RECID=18 STAMP=1082263356
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1l08jf3u_1_1 tag=TAG20210909T010055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47
Finished backup at 09-SEP-21

Starting backup at 09-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/data02.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/data01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1m08jf5e_1_1 tag=TAG20210909T010237 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 09-SEP-21

Starting backup at 09-SEP-21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=37 STAMP=1082768593
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1n08jf6h_1_1 tag=TAG20210909T010313 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-SEP-21

Starting Control File and SPFILE Autobackup at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/c-1608118455-20210909-01 comment=NONE
Finished Control File and SPFILE Autobackup at 09-SEP-21

RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1    B  F  A DISK  11-AUG-21       1       1      NO   TAG20210811T222830
4    B  F  A DISK  11-AUG-21       1       1      NO   TAG20210811T223126
7    B  F  A DISK  11-AUG-21       1       1      NO   TAG20210811T223345
10   B  F  A DISK  11-AUG-21       1       1      NO   TAG20210811T224016
12   B  F  A DISK  11-AUG-21       1       1      NO   TAG20210811T224942
28   B  F  A DISK  12-AUG-21       1       1      NO   TAG20210812T223307
30   B  0  A DISK  12-AUG-21       1       1      NO   TAG20210812T224824
32   B  1  A DISK  12-AUG-21       1       1      NO   TAG20210812T225022
34   B  1  A DISK  12-AUG-21       1       1      NO   TAG20210812T225145
36   B  F  A DISK  25-AUG-21       1       1      NO   TAG20210825T220051
40   B  F  A DISK  03-SEP-21       1       1      NO   TAG20210903T044703
47   B  0  A DISK  09-SEP-21       1       1      NO   TAG20210909T004510
52   B  1  A DISK  09-SEP-21       1       1      NO   TAG20210909T010237

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/data02.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/data01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf

Remove datafiles for testing purposes

[oratest@oracle ~]$  cd /u01/app/oracle/oradata/ORCL/
[oratest@oracle ORCL]$ ls -l
total 3099872
drwxrwxr-x. 2 oratest oratest       4096 Sep  9 01:03 backup
-rw-rw----. 1 oratest oratest   10600448 Sep  9 01:08 control01.ctl
-rw-rw----. 1 oratest oratest   10600448 Sep  9 01:08 control02.ctl
-rw-r-----. 1 oratest oratest  209715712 Sep  9 01:00 redo01.log
-rw-r-----. 1 oratest oratest  209715712 Sep  9 01:03 redo02.log
-rw-r-----. 1 oratest oratest  209715712 Sep  9 01:08 redo03.log
-rw-r-----. 1 oratest oratest 1069555712 Sep  9 01:03 sysaux01.dbf
-rw-r-----. 1 oratest oratest  975183872 Sep  9 01:03 system01.dbf
-rw-r-----. 1 oratest oratest  135274496 Sep  9 00:15 temp01.dbf
-rw-r-----. 1 oratest oratest  346038272 Sep  9 01:03 undotbs01.dbf
-rw-r-----. 1 oratest oratest    5251072 Sep  9 01:03 users01.dbf

[oratest@oracle ORCL]$ rm -rf *

Kill the DB instance, if running. You can do shut abort or kill pmon at OS level

[oratest@oracle ~]$  ps -ef  | grep pmon
ratest    3584      1  0 Sep08 ?        00:00:00 ora_pmon_test
oratest    5868      1  0 00:30 ?        00:00:00 ora_pmon_orcl
oratest    6951   4579  0 01:09 pts/0    00:00:00 grep --color=auto pmon

[oratest@oracle ~]$ kill -9 5868

[oratest@oracle ~]$ ps -ef |grep pmon
oratest    3584      1  0 Sep08 ?        00:00:00 ora_pmon_test
oratest    6994   4579  0 01:11 pts/0    00:00:00 grep --color=auto pmon

Start the DB instance and take it to the Mount stage.

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

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:47:02 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1728050048 bytes
Fixed Size                  8897408 bytes
Variable Size             436207616 bytes
Database Buffers         1275068416 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Connect to RMAN and recover the Database

[oracle@oracle ~]$ export ORACLE_SID=ORCL
[oratest@oracle ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 21:19:59 2021
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1608118455, not open)

execute the run command mention the level 0 and level 1 TAG

RMAN> run
{
RESTORE DATABASE from tag TAG20210909T004510;
RECOVER DATABASE from tag TAG20210909T010237;
RECOVER DATABASE;
sql 'ALTER DATABASE OPEN';
}
Starting restore at 08-SEP-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd0_TAG20210908T043841_jmhw7s4x_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd0_TAG20210908T043841_jmhw7s4x_.bkp tag=TAG20210908T043841
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 08-SEP-21

Starting recover at 08-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCL/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCL/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCL/undo01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd1_TAG20210908T044433_jmhwlskb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_08/o1_mf_nnnd1_TAG20210908T044433_jmhwlskb_.bkp tag=TAG20210908T044433
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

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

Finished recover at 08-SEP-21

Starting recover at 08-SEP-21
using channel ORA_DISK_1

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

Finished recover at 08-SEP-21

sql statement: ALTER DATABASE OPEN

RMAN> exit


Recovery Manager complete.

[oracle@oracle ~]$  cd /u01/app/oracle/oradata/ORCL
[oracle@oracle ]$ ls -l

total 3099872
-rw-rw----. 1 oratest oratest   10600448 Sep  9 01:08 control01.ctl
-rw-rw----. 1 oratest oratest   10600448 Sep  9 01:08 control02.ctl
-rw-r-----. 1 oratest oratest  209715712 Sep  9 01:00 redo01.log
-rw-r-----. 1 oratest oratest  209715712 Sep  9 01:03 redo02.log
-rw-r-----. 1 oratest oratest  209715712 Sep  9 01:08 redo03.log
-rw-r-----. 1 oratest oratest 1069555712 Sep  9 01:03 sysaux01.dbf
-rw-r-----. 1 oratest oratest  975183872 Sep  9 01:03 system01.dbf
-rw-r-----. 1 oratest oratest  135274496 Sep  9 00:15 temp01.dbf
-rw-r-----. 1 oratest oratest  346038272 Sep  9 01:03 undotbs01.dbf
-rw-r-----. 1 oratest oratest    5251072 Sep  9 01:03 users01.dbf

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

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:50:32 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Connect the user and check table rows are recovered, it can be done in level 1 backup

SQL> conn india/india
Connected.
SQL> select * from test;

   NO     NAME
------   -----
     1    one
     2    Two
     3    Three
     4    Four

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

LEVEL 0 INCREMENTAL BACKUP

[oratest@oracle dbs]$  rman target/

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Sep 9 00:42:53 2021
Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1608118455)

RMAN> backup incremental level 0 database plus archivelog;


Starting backup at 09-SEP-21
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=471 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=21 STAMP=1082265443
input archived log thread=1 sequence=2 RECID=22 STAMP=1082354497
input archived log thread=1 sequence=3 RECID=23 STAMP=1082412592
input archived log thread=1 sequence=4 RECID=24 STAMP=1082470208
input archived log thread=1 sequence=5 RECID=25 STAMP=1082554248
input archived log thread=1 sequence=6 RECID=26 STAMP=1082640636
input archived log thread=1 sequence=7 RECID=27 STAMP=1082677257
input archived log thread=1 sequence=9 RECID=29 STAMP=1082766943
input archived log thread=1 sequence=10 RECID=30 STAMP=1082766950
input archived log thread=1 sequence=11 RECID=31 STAMP=1082766986
input archived log thread=1 sequence=12 RECID=32 STAMP=1082766988
input archived log thread=1 sequence=13 RECID=33 STAMP=1082766991
input archived log thread=1 sequence=14 RECID=34 STAMP=1082767405
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1f08je1e_1_1 tag=TAG20210909T004325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=9 STAMP=1081792908
input archived log thread=1 sequence=14 RECID=10 STAMP=1081863051
input archived log thread=1 sequence=15 RECID=11 STAMP=1081953019
input archived log thread=1 sequence=16 RECID=12 STAMP=1082066461
input archived log thread=1 sequence=17 RECID=19 STAMP=1082263361
input archived log thread=1 sequence=18 RECID=20 STAMP=1082263367
input archived log thread=1 sequence=19 RECID=18 STAMP=1082263356
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1g08je36_1_1 tag=TAG20210909T004325 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:47
Finished backup at 09-SEP-21

Starting backup at 09-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/data02.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/data01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1h08je4m_1_1 tag=TAG20210909T004510 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
Finished backup at 09-SEP-21

Starting backup at 09-SEP-21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=35 STAMP=1082767577
channel ORA_DISK_1: starting piece 1 at 09-SEP-21
channel ORA_DISK_1: finished piece 1 at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/1i08je6p_1_1 tag=TAG20210909T004617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-SEP-21

Starting Control File and SPFILE Autobackup at 09-SEP-21
piece handle=/u01/app/oracle/oradata/ORCL/backup/c-1608118455-20210909-00 comment=NONE
Finished Control File and SPFILE Autobackup at 09-SEP-21

RMAN> list backup of database summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
--- -- -- - ----------- --------------- ------- ------- ---------- ---
1     B  F  A  DISK   11-AUG-21     1     1       NO  TAG20210811T222830
4     B  F  A  DISK   11-AUG-21     1     1       NO  TAG20210811T223126
7     B  F  A  DISK   11-AUG-21     1     1       NO  TAG20210811T223345
10    B  F  A DISK    11-AUG-21     1     1       NO  TAG20210811T224016
12    B  F  A DISK    11-AUG-21     1     1       NO  TAG20210811T224942

28    B  F  A DISK    12-AUG-21     1     1       NO  TAG20210812T223307
30    B  0  A DISK    12-AUG-21     1     1       NO  TAG20210812T224824
32    B  1  A DISK    12-AUG-21     1     1       NO  TAG20210812T225022
34    B  1  A DISK    12-AUG-21     1     1       NO  TAG20210812T225145
36    B  F  A DISK    25-AUG-21     1     1       NO  TAG20210825T220051
40    B  F  A DISK    03-SEP-21     1     1       NO  TAG20210903T044703
47    B  0  A DISK    09-SEP-21     1     1       NO  TAG20210909T004510

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

CROSSCHECK BACKUPS Using RMAN

[oratest@oracle ~]$ rman target /

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

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

connected to target database: TEST (DBID=2378581000)

RMAN> crosscheck backup;

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

Delete Expired Backups

RMAN> delete expired backup;

using channel ORA_DISK_1

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

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


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

RMAN> crosscheck backup;

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

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

RESTORE CONTROL FILE USING RMAN

Check the RMAN configuration and controlfile Auto backup Feature

[oratest@oracle ~]$ export ORACLE_SID=orcl
[oratest@oracle ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 3 04:14:07 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1608118455)

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL
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 '/u01/app/oracle/oradata/ORCL/backup/%F';
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 CHANNEL DEVICE TYPE DISK FORMAT
'/u01/app/oracle/oradata/ORCL/backup/%U' MAXPIECESIZE 8 G;
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/dbhome_1/dbs/snapcf_orcl.f'; # default

Simulate a failure when the Database is Running

[oratest@oracle ~]$ export ORACLE_SID=orcl

[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 3 04:21:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

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

SQL> select name from v$controlfile;
NAME
-------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/oradata/ORCL/control02.ctl

DELECT CONTROLFILE MANUALY

[oratest@oracle ~]$ cd /u01/app/oracle/oradata/ORCL/

[oratest@oracle ORCL]$ ls

backup control01.ctl control02.ctl redo01.log redo02.log redo03.log
sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

[oratest@oracle ORCL]$ rm -fr control01.ctl

[oratest@oracle ORCL]$ rm -fr control02.ctl

[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 3 04:27:01 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter tablespace data add datafile
'/u01/app/oracle/oradata/ORCL/test03.dbf' size 100m;
alter tablespace data add datafile '/u01/app/oracle/oradata/ORCL/test03.dbf'
size 100m
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ORCL/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> select status from v$instance;
STATUS
------------
OPEN

SQL> shut immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ORCL/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.

Keep the Database in NOMOUNT Stage and Restore the control file

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 281017392 bytes
Fixed Size 8895536 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes

Since we are not using an RMAN catalog we need to set the DBID

[oratest@oracle ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 3 04:32:06 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)

RMAN> set dbid=1608118455;
executing command: SET DBID

RMAN> restore controlfile from
"/u01/app/oracle/oradata/ORCL/backup/170840mm_1_1";
Starting restore at 03-SEP-21
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCL/control01.ctl
output file name=/u01/app/oracle/oradata/ORCL/control02.ctl
Finished restore at 03-SEP-21

Mount and Recover the database

RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> restore database;
Starting restore at 03-SEP-21
using channel ORA_DISK_1
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571:
===========================================================

starting media Recovery

archived log for thread 1 with sequence 19 is already on disk as file
/u01/app/oracle/oradata/ORCL/redo01.log
archived log file name=/u01/app/oracle/oradata/ORCL/redo01.log thread=1
sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-SEP-21
Statement processed
Use RESETLOGS after incomplete recovery (when the entire redo stream
wasn’t applied). RESETLOGS will initialize the logs, reset your log sequence
number, and start a new “incarnation” of the database.

RMAN> alter database open resetlogs;
Statement processed

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

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:

END-OF-FILE ERROR

Error code – ORA-03113

Solution

ORACLE_SID = orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 23:53:19 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  281017392 bytes
Fixed Size                  8895536 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                3686400 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4097
Session ID: 390 Serial number: 53661

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
 Production
Version 19.3.0.0.0


[oracle@oracle ~]$ sqlplus / as sysdba

Connected to an idle instance.


SQL> startup nomount
ORACLE instance started.

Total System Global Area  281017392 bytes
Fixed Size                  8895536 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                3686400 bytes

SQL> alter database mount;

Database altered.



SQL> alter database clear unarchived logfile group 2;

Database altered.


SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 9 00:14:39 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  281017392 bytes
Fixed Size                  8895536 bytes
Variable Size             218103808 bytes
Database Buffers           50331648 bytes
Redo Buffers                3686400 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORCL      READ WRITE

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Backup based Cloning a database using RMAN

1)Creating Directories

2)Verifying the RMAN backup database with control file

3)Creating pfile for clone database

4)Copying Password file for clone database

5)copy paste listener and tns for each other : prod & clone

6)Moving backup files to the directories which created for clone database

7)Connecting Rman and issue duplicate command

Target database: TEST

Cloned database: TESTDB

STEP – 1

Verifying the database name, Backups available in RMAN(Target Database)

SQL> select name,open_mode from v$database;

NAME            OPEN_MODE
--------- --------------------
TEST          READ WRITE

Verifying the database name, Backups available in RMAN(Target DB)

RMAN> SHOW CONTROLFILE AUTOBACKUP;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

RMAN> CROSSCHECK BACKUP OF DATABASE;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=79 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0106mhr1_1_1
RECID=1 STAMP=1080772449
Crosschecked 1 objects

RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.19G DISK 00:00:44 16-AUG-21
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20210816T223409
Piece Name: /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0106mhr1_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2351179 16-AUG-21 NO
/u01/app/oracle/oradata/TEST/system01.dbf
3 Full 2351179 16-AUG-21 NO
/u01/app/oracle/oradata/TEST/sysaux01.dbf
4 Full 2351179 16-AUG-21 NO
/u01/app/oracle/oradata/TEST/undotbs01.dbf
7 Full 2351179 16-AUG-21 NO
/u01/app/oracle/oradata/TEST/users01.dbf

Step 2

Creating Pfile for TESTDB (Source DB)

*.audit_file_dest='/u01/app/oracle/admin/testdb/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/TESTDB/control07.ctl','/u01/app/oracle/oradata/TESTDB/control08.ctl'
*.db_block_size=8192
*.db_name='testdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'
#*.memory_target=1024m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#*.db_recovery_file_dest size=2g
#*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_file_name_convert='/u01/app/oracle/oradata/TEST/datafile','/u01/app/oracle/oradata/TESTDB'
*.log_file_name_convert='/u01/app/oracle/oracle/oradata/TEST/onlinelog','/u01/app/oracle/oradata/TESTDB'

Step 3

Copy target database password file to Source database

[oratest@oracle ~] cp orapwtest orapwtestdb

[oratest@oracle ~] mkdir testdb

Step 4

Copying the RMAN backup pieces and control file auto backup to desired location ‘TESTDB’(source DB)

[oratest@oracle backup]$ cp c-8581000-20210824 /u01/app/oracle/oradata/testdb
[oratest@oracle backup]$ cp c-581000-20210824-02
 /u01/app/oracle/oradata/testdb
[oratest@oracle backup]$ cp 070798lh_1_1 /u01/app/oracle/oradata/testdb
[oratest@oracle backup]$ cp 090798lu_1_1 /u01/app/oracle/oradata/testdb

[oratest@oracle testdb]$ ls -l
total 1319440
-rw-rw----. 1 oratest oratest 10682368 Aug 24 01:01 070798lh_1_1
-rw-rw----. 1 oratest oratest 1318993920 Aug 24 01:02 090798lu_1_1
-rw-rw----. 1 oratest oratest 10715136 Aug 24 01:01 c-2378581000-20210824-01
-rw-rw----. 1 oratest oratest 10715136 Aug 24 01:01 c-2378581000-20210824-02

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12

[oratest@oracle dbs]$ export ORACLE_SID=testdb


[oratest@oracle dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 24 22:33:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup pfile='$ORACLE_HOME/dbs/inittestdb.ora' nomount;

ORACLE instance started.
Total System Global Area 281017392 bytes
Fixed Size 8895536 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes

SQL> exit

[oratest@oracle dbs]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 24 22:54:08 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (not mounted)

RMAN> exit

Step 5

Verifying Listener and tns entries in Source DB Entry.

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
(UR = A)
)
)

[oratest@oracle admin]$ rman target sys/oracle@test
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 24 22:55:01 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2378581000)


RMAN> connect auxiliary sys/oracle@testdb
connected to auxiliary database: TESTDB (not mounted)

Step 6

Making the clone Database in nomount state to duplicate the Database(source db)

[oratest@oracle dbs]$ export ORACLE_SID=testdb

[oratest@oracle dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 24 23:09:52 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select status from v$instance;
STATUS
------------
STARTED

[oratest@oracle dbs]$ rman auxiliary /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 24 23:11:06 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: TESTDB (not mounted)

RMAN> Duplicate target database to testdb backup location
'/u01/app/oracle/oradata/test' nofilenamecheck;
Starting Duplicate Db at 24-AUG-21
searching for database ID
found backup of database ID 2378581000
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 281017392 bytes
Fixed Size 8895536 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''TEST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''testdb'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from
'/u01/app/oracle/oradata/testdb/c-2378581000-20210824-04';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''TEST'' comment= ''Modified by RMAN
duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''testdb'' comment= ''Modified by
RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 281017392 bytes
Fixed Size 8895536 bytes
Variable Size 218103808 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Starting restore at 24-AUG-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=424 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TESTDB/control07.ctl
output file name=/u01/app/oracle/oradata/TESTDB/control08.ctl
Finished restore at 24-AUG-21
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=424 device type=DISK
RMAN-05158: WARNING: auxiliary (bctfile) file name
/u01/app/oracle/oradata/TEST/changetracking/o1_mf_jl7qx7bp_.chg conflicts with
a file used by the target database
RMAN-05158: WARNING: auxiliary (logfile) file name
/u01/app/oracle/oradata/TEST/redo01.log conflicts with a file used by the target
database
RMAN-05158: WARNING: auxiliary (logfile) file name
/u01/app/oracle/oradata/TEST/redo02.log conflicts with a file used by the target
database
RMAN-05158: WARNING: auxiliary (logfile) file name
/u01/app/oracle/oradata/TEST/redo03.log conflicts with a file used by the target
database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/TEST/system01.dbf conflicts with a file used by the target
database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/TEST/sysaux01.dbf conflicts with a file used by the target
database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/TEST/undotbs01.dbf conflicts with a file used by the target
database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/TEST/users01.dbf conflicts with a file used by the target
database
RMAN-05158: WARNING: auxiliary (tempfile) file name
/u01/app/oracle/oradata/TEST/temp01.dbf conflicts with a file used by the target
database
contents of Memory Script:
{
set until scn 2841711;
set newname for datafile 1 to
"/u01/app/oracle/oradata/TEST/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/TEST/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/TEST/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/TEST/users01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-AUG-21
using channel ORA_AUX_DISK_1
database opened
Cannot remove created server parameter file
Finished Duplicate Db at 24-AUG-21

RMAN> exit
Recovery Manager complete.

Step 7

Now the database has been successfully cloned we can verify the Database

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

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

RMAN Backup particular Datafile

RMAN Backup particular Datafile:

SQL> select tablespace_name, file_id from dba_data_files;

TABLESPACE_NAME               FILE_ID
------------------            ----------
USERS                             7
UNDOTBS1                          4
SYSTEM                            1
SYSAUX                            3
DATA                              5

RMAN>  backup datafile 1,3,5;

Starting backup at 11-AUG-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/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/data01.dbf
channel ORA_DISK_1: starting piece 1 at 11-AUG-21
channel ORA_DISK_1: finished piece 1 at 11-AUG-21
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0c069cs6_1_1 
tag=TAG20210811T224942 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
Finished backup at 11-AUG-21

Starting Control File and SPFILE Autobackup at 11-AUG-21
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1608118455-20210811-04 
comment=NONE
Finished Control File and SPFILE Autobackup at 11-AUG-21

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:

Tag Archives: create catalog

Check Database Running Parameter

Move the Spfile to Backup File

Restore the spfile from Auto backup Location

Connect rman and Take level 0 backup

Once Level 0 is completed creating a user and table insert the record for the purpose of level 1 Backup

Remove datafiles for testing purposes

Kill the DB instance, if running. You can do shut abort or kill pmon at OS level

Start the DB instance and take it to the Mount stage.

Connect to RMAN and recover the Database

execute the run command mention the level 0 and level 1 TAG

Connect the user and check table rows are recovered, it can be done in level 1 backup

Delete Expired Backups

Check the RMAN configuration and controlfile Auto backup Feature

DELECT CONTROLFILE MANUALY

Keep the Database in NOMOUNT Stage and Restore the control file

Since we are not using an RMAN catalog we need to set the DBID

starting media Recovery

Error code – ORA-03113

Solution

Target database: TEST

Cloned database: TESTDB

STEP – 1

Verifying the database name, Backups available in RMAN(Target Database)

Verifying the database name, Backups available in RMAN(Target DB)

Step 2

Creating Pfile for TESTDB (Source DB)

Step 3

Step 4

Copying the RMAN backup pieces and control file auto backup to desired location ‘TESTDB’(source DB)

Step 5

Verifying Listener and tns entries in Source DB Entry.

Step 6

Making the clone Database in nomount state to duplicate the Database(source db)

Step 7

RMAN Backup particular Datafile: