Tag: RMAN

Categories
19c database

How to change the db name using nid utility

Introduction to Nid Utility:

NID is an utility which is used to change the database internal name and dbname of a database. The utility is placed in the $ORACLE_HOME/bin directory. Prior to the nid utility we need to manually create a database and give it a new database name by recreating a control file.

Here I am going to change the db name from standby to Canada.

Overview steps:

Step:1 Mount the database with old db name(standby)

Step:2 Run the nid utility (syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME)

Step:3 Once you run the nid utility the name will be changed to new db name.(standby to Canada)

Step:4 Then you have to change the db_name in the parameter file.

Step:5 change the spfile to a new db name.

Step:6 Now open the database with reset logs option.

Step:1 mount the database with old db name.

Step:2 Run the nid utility

Syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME

Step:3 change the db_name in the parameter file.

Step:4 Rename the spfile to a new db name.

cp spfileSTANDBY.ora spfileCANADA.ora

Step:5 Open the database with resetlogs option.

Step:6 Register the database information to the listener using the command  “alter system register;”

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

 

Categories
19c database RMAN

19C RMAN CONCEPTS

19c RMAN CONCEPTS:

RECOVER DATAFILE WITHOUT RMAN BACKUP
RECOVER THROUGH RESETLOGS
BLOCK CHANGE TRACKING
RECOVER LOSS OF ALL CONTROL FILE USING AUTO BACKUP
RECOVER LOSS OF ALL ONLINE REDO LOGS 
POINT IN TIME RECOVERY 
RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE
RMAN Database Restore ASM
DATABASE INCARNATION USING RMAN
Restore Tablespace using RMAN
RESTORE SPFILE USING RMAN
RMAN backup Full Database
RMAN Backup Tablespace
RMAN Backup Particular Datafile
RMAN Backup Spfile
RMAN Backup Current Control file
RMAN Backup Archive log Until Sequence
RMAN Backup Archive log Between Sequence
RMAN Backup Archive log Between SCN
RMAN Backup Archive log Until SCN
RMAN Backup Database Plus Archive log
RMAN Backup Database Includes A Control file
RMAN Backup Archive log and All Delete Input
RMAN Backup Archive log All and Skip Inaccessible
LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN
CROSSCHECK BACKUPS Using RMAN
RESTORE CONTROL FILE USING RMAN
Backup-based Cloning of a database using RMAN
RECOVERY CATALOG DATABASE IN RMAN
RMAN ORA ERRORS
DBVERIFY
END-OF-FILE ERROR
LEVEL 0 INCREMENTAL BACKUP
point-in-time recovery using RMAN in 19c
Recover a loss of all online redo log files

 

Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter: https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Categories
19c database RMAN

Recover Datafile Without Rman Backup in Oracle 19c

Description:-

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

Step 1: New tablespace creation

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

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

FILE_NAME

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

Step 2: Take RMAN whole database backup and plus archivelog

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

RMAN> backup database plus archivelog;

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

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

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

Tablespace altered.

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

SQL> alter system switch logfile;

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

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

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

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

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

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

SQL> alter tablespace inba offline;

Tablespace altered.

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

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

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

6 rows selected.

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

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

Step 8: Check RMAN backup of INBA tablespace

RMAN> list backup of datafile 5;

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

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

RMAN> list backup of datafile 2;

specification does not match any backup in the repository

RMAN>

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

Step 9: Tablespace restore

RMAN> restore tablespace inba;

Starting restore at 31-JAN-21
using channel ORA_DISK_1

creating datafile file number=2 name=/u01/app/oracle/oradata/INBAA/inba_02.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/INBAA/inba_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T141015_j1dv7ho6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/recovery_area/INBAA/backupset/2021_01_31/o1_mf_nnndf_TAG20210131T141015_j1dv7ho6_.bkp tag=TAG20210131T141015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 31-JAN-21

RMAN>

Step 10:  Recover tablespace

RMAN> recover tablespace inba;

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

Step  11: To Bring the Tablespace online

SQL> alter tablespace inba online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS

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

6 rows selected.

SQL> select * from emp;

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

RMAN> backup database plus archivelog;


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

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

RMAN>

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

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

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

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

RMAN>

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

Categories
19c database RMAN

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

Description:-

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

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

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

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

SQL> Select filename,status from V$block_change_tracking;

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

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

Step 3: Disable block change Tracking.

SQL> alter database disable block change tracking;

Database altered.

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

no rows selected

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

Categories
19c database RMAN

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

Description:-

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

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

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

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

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

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

SQL> select member from v$logfile;

SQL> select * from v$log;

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

Step 2 : Shutdown and mount the database

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

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

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

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

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

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

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

SQL> select name,open_mode from v$database;

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

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