NOLOGGING Database Objects

Contents:- Nologging Database Objects, Recovery of a NOLOGGING Database Objects

NOLOGGING

When you use NOLOGGING keyword with any transaction at that time no redo data is generated during that particular transactions.

i.e

SQL> create table demo_stc logging;

SQL> insert /* + APPEND */ into demo_stc (select * from stc);

Recovery of Nologging Objects

We cannot recover nologging database objects. So take backup of whatever tables you populate in this way.

If You perform media recovery and there are NOLOGGING Objects involved, they will be marked logically corrupt during the recovery process. In this case drop nologging objects and re-create them.

Use the following command to list the names of any tablespace that contain one or more objects for which a NOLOGGING operation has been performed since the most recent backup of the tablespace.

SQL> Report unrecoverable;

Complete - Incomplete Recovery Concept

Contents:- Concepts, Complete Recovery, Incomplete Recovery

Complete Recovery

Complete Recovery brings the database up to the present, including all committed data changes made to the point in time when the recovery was requested.

Steps

1.   Restored Data Files.

2.   Changes applied by online(redo) and offline(archive) redo log file. (the redo log changes are applied to the data files until the current online log is reached and the most recent transactions have been reentered.  Undo blocks are generated during this process. This is called rolling forward or cache recovery.

3.   Data file containing committed and uncommitted transactions.

4.   Undo Applied ( the undo blocks are used to rollback any uncommitted changes is called as transaction recovery.

5.   Recovered Data Files.

In- Complete or Point-in Time Recovery

In Complete or Point-in Time Recovery brings the database up to a specified point in time in past, before the recovery operation was requested.

In Incomplete Recovery There are missing transactions; any data modifications done between the recovery destination time and the present are lost.

Requirements

Because in many cases there have been some changes made to the database that need to be undone. Recovering to a point in the past is a way to remove the unwanted changes.

Need

A Valid offline or online backup of all the data files made before the recovery point.

All Archive logs from the time of backup until the specified time of recovery.

Steps

1.   Restore Data files from as far back as necessary.

2.   Changes applied to point in time by using offline and online redo log file.

3.   Data file containing committed and uncommitted transactions upto PIT.

4.   Database Opened.

5.   Undo Applied.

6.   PIT- Recovered Data files.

Read only Tablespace Various Scenarios

Contents:- Read-only Tablespace, Recovering a Read-only Tablespace, Read-only Tablespace various Scenarios.

User managed Backup of Read only tablespace.

You do not have to put it in backup mode in order to make a copy of its data files.

You do not have to take the tablespace or data file offline before making a copy of it.

       Because Read-only tablespace are not being written to.

Restoring a Read-only Tablespace

 ü  When Restoring a read-only tablespace, take the tablespace offline.

 ü  Restore the data files belonging to the tablespace.

 ü  Bring the tablespace back online.

Scenarios when a Read-only Tablespace is changed to be read/write

 ü  Make a Backup of Read-only Tablespace.

 ü  Make the Tablespace Read-write.

 ü  Recover the tablespace.

Read-only Tablespace Recovery

Notes:- The method of recovering a read-only tablespace depends on the backup that are available and wheather the tablespace was altered to read/write or read/only

Case 1:- The tablespace being recovered read-only and was read-only when the last backup occurred.

In this case you can simply restore tablespace from backup, there is no need to apply the redo information.

Steps

Sql> create table demo(id number) tablespace inventory;

Sql> insert into demo values(1);

Sql> alter tablespace inventory read only;

Take fresh backup.

Disaster:- inventory tablespace lost

Sql> startup mount;

$> rman target /

Rman>restore tablespace inventory;

Sql> alter database open;

Case 2:- The tablespace being recovered is read-write, but was read-only when the last backup occurred.

In this case you need to restore tablespace from the backup and apply the redo information from the point when the tablespace made read/write.

Steps

Sql> alter tablespace inventory read only;

Take fresh backup.

Sql> alter tablespace inventory read write;

Sql> create table demo(id number) tablespace inventory;

Sql>insert into demo values(&id);

Sql>commit;

Disaster: inventory tablespace lost

Sql> startup mount force

$>rman target /

Rman>restore tablespace inventory;

Rman>recover tablespace inventory;

Sql>alter database open;

Case 3:- The tablespace being recovered read only, but was read/write when the last backup occurred.

Notes:- you should always backup a tablespace after making  it read only to avoid this situation. However if this occur, you must restore tablespace from the backup and recover upto the time that the tablespace was made read-only.

In all three cases, if the current control file is not available, the * denotes which control file backup should be used for recovery. this is necessary because when  a backup control file is used, the recovery process will require you to perform an open resetlogs. this updates the datafile headers and datafile can't be written to if they are read only.

Read-only  tablespace recovery issue

Special consideration must be taken for read-only tablespace when

i) re-creating control file

ii) renaming data file

iii) using a backup control files.

Steps

Sql> create table demo(id number) tablespace inventory;

Insert records into demo table and commit;

Take normal backup.

Sql> insert into demo values(&id);

Sql> commit;

Sql> alter tablespace inventory read only;

Disaster:- all controlfile deleted.

$> rman target / catalog rman_user/rman_user@dbc

Rman> run

{

Startup nomount;

Restore controfile from autobackup;

Sql ‘alter database mount’;

Recover database;

Sql ‘alter database open resetlogs’;

}

Disaster:- inventory tablespace lost;

Rman>restore tablespace inventory;

Rman>recover tablespace inventory;

Sql>alter database open;

Index Tablespace Concept, Recovery, Create Index

Contents:- Concept of index tablespace, Index Tablespace Recovery, Creating Index

Concept

A tablespace that contains only indexes may be recovered without performing a RECOVER Task.

Recovery Steps if a Datafile belonging to an index tablespace is lost

 1.   Drop the data file.

 2.   Drop the tablespace.

 3.   Re-create the index tablespace.

 4.   Re-Create the indexes that were in the tablespace.

Creating Index

SQL> Create index demo_index on demo(salary);

Options

PARALLEL

(NOPARALLEL is the Default):- when you use parallel option at that time multiple server process simultaneously used to create an index. So by using parallel option you can create index more fastly.        

NOLOGGING

Using this option index can be created faster, Because it creates a very minimal amount of redo log entries as a result of the creation process.

SQL> Alter index demo_index nologging/ logging;

Notes:- NOLOGGING can be overwritten, if you are using Data Guard or Force Logging at the database or tablespace level.

You can use Data Pump Export with the CONTENT=METADATA_ONLY parameter to create a dump file containing the SQL commands to re-create the index.

Contents:- Create Backup in Different Ways i.e Backup Set, Image Copy, Archival Backup, Multisection Backup into multipiece backup set, Incremental backup, leve 0 Backup, Level 1 Cumulative Backup, Level 1 Differential Backup

Backup Types

Full Backup                             

Contains all data file blocks.

Level 0 Incremental Backup     

Contains all data file blocks same as full backup but the base for incremental backup.

Level 1 Cumulative Incremental Backup      

Contains only blocks modified since the last level 0 incremental backup.

Level 1 Differential Incremental Backup

Contains only blocks Modified since the last incremental backup(either level 0 or 1).

Creating Backup Sets

Backup set is a collection of one or more files of backup.

RMAN> Backup as backupset format ‘/backup/df_%d_%s_%p.bus’ tablespace scm_data;

Creating Image Copies Backup

Image copy backup means same to same (block to block) backup of a file same as by operating system copy and paste command.

An image copy is a clone of single Datafile, archivelog , redo log or control file.

An image copy can be written only to disk. In Image copy backup time is increased because all blocks are copies whether they contain data or not, but restoration time is reduced. Here oracle database process copies the file and perform additional actions such as Checking for Corrupt blocks and registering the copy in the control file.

RMAN> Configure device type disk backup type to copy;

or

RMAN> Backup as copy Datafile ‘/u01/app/backup/users_01_db01.dbf’;

RMAN> Backup as copy archivelog like ‘/u01/app/backup/arch%’;

To Speedup the process of copying , you can use the NOCHECKSUM Parameter. By default RMAN Computes the checksum of each block backed up, and stores it with the backup. When the backup is restored the checksum(Is mainly used to detect the error) is verified.

An Image copy can be a part of a Full or Incremental Level 0 Backup because a file copy always includes all blocks.

Creating a Whole Database Backup

RMAN> Backup database plus archivelog DELETE INPUT.

9Or

RMAN> run

{

Backup database format device type disk to ‘/u01/app/backup/db_%u_%d_%s’;

Sql ‘alter system archive log current’;

Backup format ‘/u01/app/oracle/backup/archivelog/log_t%t_s%s_%p%p’

(archivelog all delete all input);

}

Fast Recovery Area

Fast Recovery Area would perform space management for you, deleting files when space pressure grows.

Create a Backup (Backup set or image copies) of previous image copies of all files.

RMAN> Backup copy of database;

Incremental Backup

Incremental backup is either level 0 or level 1(cumulative or differential(default)).

RMAN> Alter database enable block change tracking  using file ‘/u01/app/oracle/incremental_backup/record.cts’;

We can rename this block change tracking file but only when the database is in mount stage.

RMAN> backup incremental level 0 database;

RMAN> backup incremental level 1 database;

RMAN> backup incremental level 1 cumulative database;

Monitor Block Change Tracking

SQL> select filename,status,bytes from V$block_Change_Tracking;

SQL> select file#,avg(Datafile_blocks),avg(blocks_read),avg(blocks_read/Datafile_blocks)*100 PCT_READ_FOR_BACKUP, avg(blocks) from V$backup_datafile where used_change_tracking=’YES’ and incremental_level>0 group by file#;

V$backup_datafile view shows how effective the block change tracking is in minimizing the incremental backup I/O (PCT_READ_FOR_BACKUP column). A high value indicates that RMAN reads most blocks in the data file during the incremental backup. You can reduce this ratio by decreasing the time between the incremental backups.

Full Image copy can be incrementally updated

A Full image copy can be incrementally updated by applying incremental backups with the RECOVER Command.

Creating Duplex Backup Set

RMAN> backup as backupset device type sbt copies 2 incremental level 0 database;

Creating Backups of Backup Sets

RMAN> backup device type disk as backupset database plus archivelog;

RMAN> backup device type sbt backupset all;

Skip Read only table space during Backup.

You can specify SKIP READONLY Option of the backup command to let RMAN know do not backup read-only tablespace.

Creating RMAN Multisection Backup.

During the multisection backup a large Datafile is divided into number of section. And each section backup separately into multipiece backupset.

Oracle Datafile can be upto 128 TB in size.

RMAN break up these large files into multiple section and backup and restore these section independently by creating multisection backup.

Multisection backup can only be done with backup sets not with image copies.

Backing up a file into separate sections can improve the performance of the backup operation, and it also allows large files backups to be restarted.

A Multisection backup job produce a multipiece backup sets. Each piece contains one section of the file. All sections of a multisection backup, except for the last section, are of the same size. There are maximum 256 sections per file.

Compatible must be set to at least 11.0

Syntax:

BACKUP <options> SECTION SIZE <integer> [K | M | G]

VALIDATE DATAFILE <options> SECTION SIZE <integer> [ K | M | G ]

Practical

RMAN> backup Datafile 3 section size=25m TAG ‘section25mb’;

Viewing Metadata about multi section backup

V$BACKUP_SET, RC_BACKUP_SET     -

Views have a MULTI_SECTION Column. Which indicates whether this is a multisection backup or not.

V$BACKUP_DATAFILE and RC_BACKUP_DATAFILE

Views have a SECTION_SIZE Column. Which specifies the number of blocks in each section of a multisection backup. Zero means a whole file backup.

Archival Backup

If you need to preserve an online backup for a specified amount of time.  RMAN Automatically assumes that you might want to perform point-In time recovery for any issue since that backup to the present. To Satisfy this RMAN keeps archive log for that time period.

Concepts

Requirements:

Simply keep the specific backup. i.e for two years.  You do not have intention of recovering to a point in time since that backup, but you just want to be able to recover to the exact time of the backup and no later. And parallel you also want to maintain a retention policy that keeps your backup area free of clutter, so making it reach back two years is not acceptable.

Solution

An Archival backup solve this problem. If you mark a backup as an archival backup, that attributes overrides any configured retention policy for the purpose of this backup. This archival backup either considered obsolete only after the time you specified or never.

The KEEP Clause creates an archival backup that is a snapshot of the database at a point in time. The only redo logs that are kept are those required to restore this backup to a consistent state.

Backup created with the KEEP Option include the SPFILE, Control Files and Archived Redo Log Files required to restore this backup and data files.

After the Archival backup is created, it is retained for as long as specified. Even if you have much smaller retention window and run DELETE OBSOLETE command, the archival backup remains.

Archive backup cannot be written to fast recovery area.

Use

The Archival backup can be used to restore the database to another host for testing purpose.

Creating Archival Backups with RMAN

Syntax:

KEEP {FOREVER | UNTIL TIME [=] ‘date_string ‘}

NOKEEP

[ RESTORE POINT rname ]

Practical

Archiving the Database Backup

RMAN> connect target / catalog rman_user/rman_user@catdb

Rman> change backup tag ‘consistent_db_backup’ keep forever; (This is Changes a consistent backup into an archival backup)

Changing the status of a database copy

Rman> change copy of database controlfile NOKEEP; (It will change control file status from keep to nokeep so now it not exempt from the existing retention policy and eligible for obsolete status).

List all Restore Points

RMAN > list restore point all;

Or

RMAN> list restore point ‘restore_point name’;

Backing up Recovery Files.

Backup only the files in the fast recovery area

RMAN> backup recovery area;

Back up all Recovery Files.

RMAN> backup recovery files;

RMAN Backup Related Commands

RMAN>  LIST         Display information about backup sets, copies and image copies recorded in the repository.

RMAN>  REPORT    Produces a detailed analysis of the repository

RMAN> report need backup-  list all data files that requires a backup.

RMAN> report obsolete;  identifies the files that are no longer needed to satisfy backup retention policy.

Report obsolete command reports which  files are obsolete under the currently configured retention policy.

RMAN> crosscheck                   Verifies the status of backup and copies recorded in the RMAN Repository against media such as disk or tape.

RMAN> delete expired     Removes files whose status is expired in rman repository.

RMAN> delete obsolete    Delete backups that are no longer needed.

Dynamic Performance Views Related to Backup.

Target Database

V$backup_set        V$backup_piece    V$datafile_copy     V$backup_files

Recovery Catalog Database

Rc_backup_set       rc_backup_piece    rc_datafile_copy    rc_backup_files

In order to query the RC_BACKUP_FILES view, first execute following command in recovery catalog database.

Sql> call dbms_rcvman.setdatabase(null,null,null,<dbid>);