Change PDB name with DBNEWID Utility in 19c


DBNEWID Utility:

NID utility has been introduced with Oracle 10g to change the database name and id. Without NID changing the DBNAME is only possible by recreating the control files. Changing the DBID was not possible before, as this is the database unique identifier. Giving a database a new DBNAME is common after migration of a database instance using Data Guard or duplicate where DBNAME and DB_UNIQUE_NAME differs and shall be synchronized afterwards. In some situation an ORA-01103 error occurs and changing the DBNAME to a new value may be needed.

OVERVIEW:

  1. Verifying the pluggable database
  2. For rename the pluggable database, We need to open the database in Restricted Mode
  3. Set the container database name
  4. Renaming the pluggable database
  5. Open the pluggable database

Note : For an example , I am taking Source PDB “Mumbai” and renaming as “Noida”,  

Step 1:

Verifying the available pluggable database in our local CDB

SQL> select con_id,name,open_mode from v$Pdbs;

Step 2:

Before we need to rename the pluggable database ,We should bring the PDB close and open it in “RESTRICTED MODE”

alter pluggable database mumbai close;

Step 3:

Opening the PDB in restricted mode,

alter pluggable database mumbai open restricted;

Step 4:

Set the PDB “MUMBAI”

alter session set container=mumbai;

Step 5:

Here we are renaming the pluggable database name “mumbai” as “noida”

alter pluggable database rename global_name to noida;

Step 6:

Here we can check the renamed pdb by issuing the below command

select con_id,name,open_mode from v$Pdbs;

But , the database is in restricted mode so we need to open the database,

Now the database name has been changed .

Oracle Database In-Memory concepts in 19c

Version 19.8 has below updates:

New Features

The following major features are new in this release.

  • Database In-Memory Base LevelStarting with Oracle Database release 19c, version 19.8, you can enable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
  • Starting with Oracle Database release 19c, version 19.8, you can use the CellMemory feature without enabling the IM column store by setting INMEMORY_FORCE=CELLMEMORY_LEVEL and INMEMORY_SIZE=0. With these settings, the IM column store is not enabled and queries can use CellMemory to scan objects.In previous releases of Oracle Database, in order to use the CellMemory feature, you were required to enable the IM column store, even if you had no intention of using it. This incurred the overhead of enabling the IM column store without any benefit.

 

Changes in Oracle Database Release 19c, Version 19.1

New Features

  • Database In-Memory Base LevelEnable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
  • Database In-Memory wait on populateThe DBMS_INMEMORY_ADMIN.POPULATE_WAIT function initiates population of all INMEMORY objects that have a priority greater than or equal to the specified priority, and returns a status value for the population. A user-specified interval specifies the maximum time that the function waits before returning the value to the caller.
    • The ORACLE_HIVE and ORACLE_BIGDATA drivers are supported.
    • Parallel query is supported.
    • A full table scan populates an In-Memory external table. In previous releases, population required using the POPULATE or REPOPULATE procedure of DBMS_INMEMORY.
    • The In-Memory background processes, rather than a foreground process, now drop IM segments.Big Data and performance enhancements for In-Memory external tablesThis release introduces several manageability and performance improvements to the In-Memory external tables feature:
  • Hybrid partitioned tablesPartitions can reside in both Oracle Database segments and in external files and sources. This feature significantly enhances partitioning for Big Data SQL, where large portions of a table can reside in external partitions. Only the internal partitions of a hybrid partitioned table inherit the INMEMORY attribute.
  • Oracle Database Resource Manager automatically enabled for Database In-MemoryWhen INMEMORY_SIZE is greater than 0, the Resource Manager is automatically enabled.
  • Oracle Data Guard Multi-Instance Redo Apply supports the IM column storeSetting the initialization parameter ENABLE_IMC_WITH_MIRA to TRUE enables the IM column store and Data Guard Multi-Instance Redo Apply at the same time on an Active Data Guard standby database. By default, ENABLE_IMC_WITH_MIRA is FALSE.
 

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

Oracle Database In-Memory concepts in 19c

Version 19.8 has below updates:

New Features

The following major features are new in this release.

  • Database In-Memory Base LevelStarting with Oracle Database release 19c, version 19.8, you can enable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
  • Starting with Oracle Database release 19c, version 19.8, you can use the CellMemory feature without enabling the IM column store by setting INMEMORY_FORCE=CELLMEMORY_LEVEL and INMEMORY_SIZE=0. With these settings, the IM column store is not enabled and queries can use CellMemory to scan objects.In previous releases of Oracle Database, in order to use the CellMemory feature, you were required to enable the IM column store, even if you had no intention of using it. This incurred the overhead of enabling the IM column store without any benefit.

 

Changes in Oracle Database Release 19c, Version 19.1

New Features

  • Database In-Memory Base LevelEnable the Database In-Memory Base Level by setting the INMEMORY_FORCE initialization parameter to BASE_LEVEL. The Base Level enables you to experiment with In-Memory features without purchasing the Oracle Database In-Memory option.When the Base Level is enabled, the IM column store size is limited to 16 GB for a CDB or non-CDB and for every database instance in an Oracle RAC database. Also, the compression level for all objects and columns is set to QUERY LOW automatically and transparently, and Automatic In-Memory is disabled. The CELLMEMORY feature is disabled for Oracle Exadata.
  • Database In-Memory wait on populateThe DBMS_INMEMORY_ADMIN.POPULATE_WAIT function initiates population of all INMEMORY objects that have a priority greater than or equal to the specified priority, and returns a status value for the population. A user-specified interval specifies the maximum time that the function waits before returning the value to the caller.
    • The ORACLE_HIVE and ORACLE_BIGDATA drivers are supported.
    • Parallel query is supported.
    • A full table scan populates an In-Memory external table. In previous releases, population required using the POPULATE or REPOPULATE procedure of DBMS_INMEMORY.
    • The In-Memory background processes, rather than a foreground process, now drop IM segments.Big Data and performance enhancements for In-Memory external tablesThis release introduces several manageability and performance improvements to the In-Memory external tables feature:
  • Hybrid partitioned tablesPartitions can reside in both Oracle Database segments and in external files and sources. This feature significantly enhances partitioning for Big Data SQL, where large portions of a table can reside in external partitions. Only the internal partitions of a hybrid partitioned table inherit the INMEMORY attribute.
  • Oracle Database Resource Manager automatically enabled for Database In-MemoryWhen INMEMORY_SIZE is greater than 0, the Resource Manager is automatically enabled.
  • Oracle Data Guard Multi-Instance Redo Apply supports the IM column storeSetting the initialization parameter ENABLE_IMC_WITH_MIRA to TRUE enables the IM column store and Data Guard Multi-Instance Redo Apply at the same time on an Active Data Guard standby database. By default, ENABLE_IMC_WITH_MIRA is FALSE.
 

Reference : docs.oracle.com 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

AutoUpgrade 21c Release: New Features/Enhancements

Belong to Below environment:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Oracle Database – Standard Edition – Version 12.2.0.1 and later

Overview:

Oracle Database AutoUpgrade allows DBAs to upgrade one or many databases without human intervention, all with one command and a single configuration file.

Advantages:

AutoUpgrade enables customers to upgrade one or many Oracle databases at the command-line with a single command and a single configuration file. AutoUpgrade will run the preupgrade tasks, perform automated fixups where needed, execute the database upgrade, and finish by taking care of post-upgrade tasks. It includes automatic retry and fallback, the possibility to schedule upgrades for future points in time, and the ability to set, change or remove initialization parameters as desired. It saves time and money by upgrading hundreds of databases with one command and replacing bespoke high maintenance upgrade solutions.

Download

The most recent version of AutoUpgrade can be downloaded via this link: version 20201214.

Supported Target Versions

AutoUpgrade Tool can be used on upgrading to below Oracle Database releases:

  • Oracle Database 21c (21.3 and newer)
  • Oracle Database 19c (19.3 and newer)
  • Oracle Database 18c (18.5 and newer)
  • Oracle Database 12c Release 2 (12.2 + DBJAN2019RU and newer)

How to Install

As of the January 2019 Release Updates (DBJAN2019RU) and beyond, AutoUpgrade Support is available for Oracle Database 12c Release 2 (12.2) and Oracle Database 18c (18.5) target homes. For both Oracle Database 12c Release 2 (12.2) and Oracle Database 18c (18.5) target homes, you must download the AutoUpgrade Tool and place it under $ORACLE_HOME/rdbms/admin.

In Oracle Database 19c (19.3) and later target Oracle homes, the autoupgrade.jar file exists by default. However, before you use AutoUpgrade, Oracle recommends that you download the latest version and replace the one already existing under $ORACLE_HOME/rdbms/admin.

Limitations

  • Database restoration option is not available on an Oracle Database Standard Edition.

AutoUpgrade 21c Release: New Features/Enhancements

  • Non-CDB to PDB Upgrades
    • AutoUpgrade can upgrade and convert a non-CDB to a PDB in a new CDB in a single operation, or upgrade and then convert a Non-CDB database to a PDB in a pre-existing CDB
    • Support for RAC and SI databases
  • Unplug-plug upgrade
    • AutoUpgrade can perform an unplug of a pluggable database (PDB) from an earlier release source container database (CDB), plug it into a later release target CDB, and then complete all the steps required to upgrade the PDB to the target CDB release
  • Supports RAC upgrades (Only for Linux or Unix-based systems)
    • Configuration of the RAC management system in the source and target home is automated
    • Supports native file systems, ASM and ACFS
    • Oracle Restart
    • Pluggable databases are supported in a RAC environment
  • Ability to pass catctl_options (via config file)
    • This enables the DBA to control the level of parallelism for a specific upgrade.
  • Restore capability
    • AutoUpgrade restore job option allows the DBA to restore database back to source home if upgrade failed or succeeded
  • Clear the recovery for a specific job by adding clear_recovery_data on the command line and use jobs parameter to specific exactly which jobs recovery data must be cleared.
  • Upgrades on Data Guard environments
    • AutoUpgrade can detect Oracle Data Guard configurations, and defer shipping logs to standby databases configured for the primary database. It will also detect and defer shipping logs to standby database if the deployment is configured manually. Upon a successfully upgrade of the primary database, upgrades to the standby database must be performed and monitored by the DBA.
  • AutoUpgrade performance improvements
    • utlrp compilations moved out of upgrade into a post fixup
    • Improved resume operations: AutoUpgrade keeps track and skips over PDBs databases that have been upgraded successfully
    • Replay support was added for upgrades 21 or higher
  • AutoUpgrade supports upgrade on CDBs with proxy pdbs
  • AutoUpgrade supports upgrade on CDB’s with application containers
  • Added additional JSON status information
  • Integrated classic pre-upgrade functionality
    • The -preupgrade clause of AutoUpgrade replaces the functions previously performed by the manual Pre-Upgrade Information Tool (preupgrade.jar) in previous releases The -mode clause takes one of three values:
      • analyze: Check your system for readiness to upgrade
      • fixups: Perform fixups as needed on your source Oracle Database release in preparation for upgrade
      • postfixups: Perform fixups on your target Oracle Database release after upgrade is completed.
      • Reports are identical to what the preupgrade.jar originally produced.
  • Starting Oracle Release 21c, Enterprise Manager, DBUA and ORAchk use AutoUpgrade to perform database upgrade readiness.
  • Starting Oracle Release 21c, FPP performs database upgrades using AutoUpgrade
  • Enhanced management of databases using Transparent Data Encryption (TDE)

Reference oracle support Doc id :  2485457.1

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

Recover Datafile Without Rman Backup in Oracle 19c

Description:-

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

Step 1: New tablespace creation

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

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

FILE_NAME

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

Step 2: Take RMAN whole database backup and plus archivelog

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

RMAN> backup database plus archivelog;

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

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

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

Tablespace altered.

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

SQL> alter system switch logfile;

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

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

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

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

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

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

SQL> alter tablespace inba offline;

Tablespace altered.

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

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

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

6 rows selected.

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

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

Step 8: Check RMAN backup of INBA tablespace

RMAN> list backup of datafile 5;

List of Backup Sets

BS Key Type LV Size Device Type Elapsed Time Completion Time


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


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

RMAN> list backup of datafile 2;

specification does not match any backup in the repository

RMAN>

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

Step 9: Tablespace restore

RMAN> restore tablespace inba;

Starting restore at 31-JAN-21
using channel ORA_DISK_1

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

RMAN>

Step 10:  Recover tablespace

RMAN> recover tablespace inba;

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

Step  11: To Bring the Tablespace online

SQL> alter tablespace inba online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS


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

6 rows selected.

SQL> select * from emp;

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

RMAN> backup database plus archivelog;


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

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

RMAN>

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

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

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

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

RMAN>

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

RMAN recovery through RESETLOGS

Description:- 

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

What does a RESETLOGS do?

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

The Recovery through Resetlogs feature provides the following benefits:

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

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

SQL> show parameter log_archive_format

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

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

What is an incarnation?

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

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

[oracle@localhost scripts]$ rman target /

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

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


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

RMAN>

Step 3:- Note the CURRENT_SCN of the database.

SQL> select current_scn from v$database;

CURRENT_SCN

2406389

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

Simulate the wrong DML execution,

SQL> conn inba/inba
Connected.

SQL> create table student(id number);

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

SQL> select count(*) from student;

COUNT(*)

100000
SQL> delete from student where rownum < 1001;

1000 rows deleted.
SQL> commit;

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

COUNT(*)

99000
SQL> conn / as sysdba
Connected.

Step 5:- Shutdown and mount the  database

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

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

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

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

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

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

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

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

RMAN> alter database open resetlogs;

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

COUNT(*)

100000

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

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


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

Step 9:- Reset incarnation of database.

RMAN> reset database to incarnation 10;

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

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

Step 11:- Open the database using resetlogs option

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

SQL> alter database open resetlogs;

Database altered.

RMAN> list incarnation of database;

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


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

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

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

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

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

Description

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

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

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

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name INBAA are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/19.0.0/dbs/snapcf_inbaa.f’; # default

RMAN>

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

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

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

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

OPEN_MODE NAME


READ WRITE INBAA

SQL> select name from v$controlfile;

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

[oracle@localhost INBAA]$ sqlplus / as sysdba

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

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

STATUS

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

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

SQL> startup nomount;
ORACLE instance started.

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

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

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

RMAN> set dbid=936024966;

executing command: SET DBID

RMAN> restore controlfile from autobackup;

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

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

Step 5:- Mount and recover the database

RMAN> alter database mount;

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

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

Step 7:- Open the database using resetlogs option

RMAN> alter database open resetlogs;

Statement processed

RMAN>

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

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

Recover 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