Bollipelly PruthviRaj Goud: Oracle Database 11g: New Features in RMAN :

Oracle Database 11g: New Features in RMAN :

__________________________________________

RMAN> list failure;

RMAN> advise failure;

RMAN> repair failure preview;

RMAN> repair failure;

RMAN> validate database;

6.backup of datafile Parallelism on same datafile.

7.Optimized backup of undo tablespace(commited undo data not going to take the backup).

8.Faster Backup Compression

9.Duplicate from ACTIVE database

RMAN>DUPLICATE DATABASE TO DB11G FROM ACTIVE DATABASE;

10.Virtual Private Catalog

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

Block corruption:

=---------------------

SQL> select * from v$backup_corruption;

RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK#

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

BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO

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

1 681247195 681247006 4994 1 17 337317

1 0 YES FRACTURED

Block recovery can only be done using RMAN.

The entire database can be open while performing block recovery.

RMAN>

run {

allocate channel c1 type 'SBT_TAPE'

parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

allocate channel c2 type 'SBT_TAPE'

parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

blockrecover datafile 17 block 337317;

release channel c1;

release channel c2;

}

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

Archive log backup:

View: V$ARCHIVED_LOG

backup archivelog from sequence 34324 untill sequence 35000 delete input;

RMAN>backup archivelog sequence between n and n2 thread N delete input;

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

SQL> alter system set log_archive_dest_1='LOCATION=/oratest/TESTDB/arch' scope=memory;

System altered.

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

DIFFERENCE B/W OBSOLETE BACKUP AND EXPIRED BACKUP:

An obsolete backup is no longer needed according to the user’s retention policy.

An expired backup is a backup that the CROSSCHECK command fails to find on the specified media device.

An obsolete backup differs from an expired backup.

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

RMAN Restores:

RMAN> SET DBID 320066378;

RMAN> RUN {

SET CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE DISK TO 'autobackup_format';

RESTORE CONTROLFILE FROM AUTOBACKUP;

}

Examples

Restore and recover the whole database

RMAN> STARTUP FORCE MOUNT;

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN;

Restore and recover a tablespace

RMAN> SQL 'ALTER TABLESPACE users OFFLINE';

RMAN> RESTORE TABLESPACE users;

RMAN> RECOVER TABLESPACE users;

RMAN> SQL 'ALTER TABLESPACE users ONLINE';

Restore and recover a datafile

RMAN> SQL 'ALTER DATABASE DATAFILE 64 OFFLINE';

RMAN> RESTORE DATAFILE 64;

RMAN> RECOVER DATAFILE 64;

RMAN> SQL 'ALTER DATABASE DATAFILE 64 ONLINE';Steps for media recovery:

Specify the new location for each datafile

SET NEWNAME FOR DATAFILE '/olddisk/users01.dbf' TO '/newdisk/users01.dbf';

“DUPLICATE from ACTIVE Database Using rman Step-by-Step

rman target sys/sys@HRDEV auxiliary sys/sys@hrprd1

RMAN>

run

{

DUPLICATE TARGET DATABASE TO HRPRD FROM ACTIVE DATABASE;

}

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

********Recovery From Loss Of Datafile For Which No Backup Is Available

$ rm /PERF/u02/oradata/PERF/users02.dbf

> startup mount

> alter database datafile 5 offline;

> alter database open;

> alter database create datafile ‘/PERF/u02/oradata/PERF/users02.dbf’;

> recover datafile ‘/PERF/u02/oradata/PERF/users02.dbf’;

auto

> alter database datafile ‘/PERF/u02/oradata/PERF/users02.dbf’ online;

Done.

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

*****You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file?

create the datafile and recover that datafile.

SQL> alter database create datafile ‘…path..’ size n;

RMAN> recover datafile file_id;

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

*******Point-In-Time- Recovery

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

connect catalog rman/testur01@rcat9

connect target rman/number9@TEST022

connect auxiliary sys/sairam@R920_32

run {

set until time '2009 OCT 29 05:02:17';

allocate auxiliary channel t1 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=hsun10,NSR_CLIENT=hsun50)';

allocate auxiliary channel t2 type 'SBT_TAPE'

parms 'ENV=(NSR_SERVER=hsun10,NSR_CLIENT=hsun50)';

set newname for datafile 1 to '/opt/oracle/oradata/R920_32/data1/1.dbf';

set newname for datafile 2 to '/opt/oracle/oradata/R920_32/data1/2.dbf';

set newname for datafile 3 to '/opt/oracle/oradata/R920_32/data1/3.dbf';

duplicate target database to R920_32

logfile

GROUP 1 ('/opt/oracle/oradata/R920_32/log1/redo/log_1.rdo') SIZE 200M,

GROUP 2 ('/opt/oracle/oradata/R920_32/log2/redo/log_2.rdo') SIZE 200M;

}

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

********Oracle 10g database.One table is dropped one hour ago by mistake and we don't have the backup and flash back feature is not enabled?How you will able to recover?

1.select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime from recyclebin;

flashback table tst to before drop;

2.Suppose the table is not available in recycle bin aslo flashback is not enable.But if you have recent RMAN backup containing table.

you can recover the table by creating a duplicate database from production database using until log sequenece

RMAN> RUN

2> {

3> SET UNTIL SEQUENCE 11 THREAD 1;

4> DUPLICATE TARGET DATABASE TO "clone" NOFILENAMECHECK;

5> }

then

export the users table from clone(duplicate) database.

import the users table to the production database.

3.Restore from v$recycle_bin or If you have any DEV or UAT or standby database, export and import into production database.

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

Loss of spfile and pfile and no backup:

1.I found that there is a command to create file from memory!!!(11g new feature!!!)

SQL> create spfile='/db01/apps/oracle/admin/create/test_file' from memory;

My database is 10g.. So I need to create it manually :(

2.Using RMAN we can create a new spfile.

Now try with RMAN

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

[DB11203-11.2.0 @ mymachine : rman target /

RMAN> startup

startup failed: ORA-01078: failure in processing system parameters

Copy the file from cold backup location

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/product/11.2.0/11.2.0.3/dbs] cd /u01/app/oracle/database/DB11203/

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203]

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] ls

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

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] cp control01.ctl control02.ctl /u01/app/oracle/product/11.2.0/11.2.0.3/dbs

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] sqlpus "/as sysdba"

SQL> shut abort

ORACLE instance shut down.

[DB11203-11.2.0 @ mymachine : /u01/app/oracle/database/DB11203] rman target /

RMAN> startup

Oracle instance started

database mounted

database opened.

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

**********I've lost an archived/online REDO LOG file, can I get my DB back?

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

************d) Online redolog file in CURRENT state:-

The LGWR will be terminated in this case. We would have to perform incomplete recovery.

Steps for complete recovery:-

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

1. Using RMAN

2. Using multiplex copy of redolog file

a) Using RMAN:-

Connect to the target database and perform a recovery.

Commands:-

D:\>rman target sys/@

RMAN> RESTORE DATABASE;

RMAN>RECOVER DATABASE;

RMAN>ALTER DATABASE OPEN;

b) Using Backup of redolog file:-

Apply the multiplex copy of redolog file and start the database.

SQL> alter database open;

Steps for Incomplete recovery:-

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

1. First find out the first change for the redolog file. This would be the SCN number.

SQL>select first_change, group# from v$log where group#=;

FIRST_CHANGE#

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

123456

2. Connect to RMAN and perform the incomplete recovery until SCN number -1;

Command:-

C:\>rman target sys

RMAN> RESTORE DATABASE UNTIL SCN ;

RMAN> restore database until scn 123455;

RMAN> recover database until scn 123455;

RMAN> alter database open resetlogs;

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

*********Temporary tablespace full 100%, How to shrink Default temporary tablespace

If the temporary tablespace you want to shrink is your default temporary tablespace, you will have to first create a new temporary tablespace, set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it. Afterwords drop the second temporary table created.

There are 3 possibilities:

1) set the TEMP files to autoextend or add TEMP files

2) shrink the TEMP file(s)

It is not clear what your want and why. In the meantime:

3) re-create it.

4) leave things as they are. There may be no problem at all.

SQL> CREATE TEMPORARY TABLESPACE temp2

2 TEMPFILE '/the/full/path/to/temp2_01.dbf' SIZE 5M REUSE

3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp

2 TEMPFILE '/the/full/path/to/temp01.dbf' SIZE 256M REUSE

3 AUTOEXTEND ON NEXT 128M MAXSIZE unlimited

4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Note -- SHRINK SPACE is introduced in 11g, and is not available in 10g.

alter tablespace <your_temp_ts> shrink space keep 128M;

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

1.Proactive Health Checks

In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, can check database blocks for physical corruption.

RMAN> validate database;

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

2.Advice on recovery

To find out failure...

RMAN> list failure;

To get the advice on recovery

RMAN> advise failure;

Recovery Advisor generates a script that can be used to repair the datafile or resolve the issue. The script does all the work.

To verify what the script actually does ...

RMAN> repair failure preview;

RESTORE DATABASE PREVIEW;

RESTORE TABLESPACE users PREVIEW;

RESTORE DATAFILE 3 PREVIEW;

RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;

RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;

RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;

Now execute the actual repair by issuing...

RMAN> repair failure;

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

3.Parallel backup of the same datafile.

In 10g each datafile is backed by only one channel. In Oracle Database 11g RMAN, the multiple channels can backup one datafiles parallel by breaking the datafile into chunks known as "sections."

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

4.Optimized backup of undo tablespace.

In 10g, when the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed.

In Oracle Database 11g, RMAN bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup.

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

5.Improved Block Media Recovery Performance

If flashback logs are present, RMAN will use these in preference to backups during block media recovery (BMR), which can significantly improve BMR speed.

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

6.Block Change Tracking Support for Standby Databases

Block change tracking is now supported on physical standby databases, which in turn means fast incremental backups are now possible on standby databases.

*****how will block change Tracking (Background Process) helps in RMAN incremental backups......?

Block change tracker will maintain the list of all blocks that were changed from the last backup, so when RMAN is taking the backup it will find changed from block change tracker and take backup of those blocks.

A background Process called CTWR gets enabled whenever u modify the data.This CTWR updates modified block to a seperate file at OS level.Whenever you take a backup RMAN takes the backup of modified blocks witout reading the entire datafile ,it gets information from CTWR.....

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

7.Faster Backup Compression

RMAN now supports the ZLIB binary compression algorithm as part of the Oracle Advanced Compression option. The ZLIB algorithm is optimized for CPU efficiency, but produces larger zip files than the BZIP2 algorithm available previously, which is optimized for compression.

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

8.Archived Log Deletion Policy Enhancements

The archived log deletion policy of Oracle 11g has been extended to give greater flexibility and protection in a Data Guard environment. The Oracle 10g and Oracle 11g syntax is displayed below.

# Oracle 10g Syntax.

CONFIGURE ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON STANDBY | NONE}}

# Oracle 11g Syntax.

ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON [ALL] STANDBY |BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}[ {APPLIED ON [ALL] STANDBY | BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |NONE | SHIPPED TO [ALL] STANDBY}]...}

The extended syntax allows for configurations where logs are eligible for deletion only after being applied to, or transferred to, one or more standby database destinations.

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

9.Duplicate from ACTIVE database

rman target sys/sys@HRDEV auxiliary sys/sys@hrprd1RMAN>

run

{

DUPLICATE TARGET DATABASE TO HRPRD

FROM ACTIVE DATABASE;

}

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

10.Virtual Private Catalog

We can grant restricted access to the RMAN catalog to some users so that they can only access a limited set of databases that are registered in the RMAN catalog.

This is done by creating a Virtual Private Catalog which in turn will grant a particular user read/write access to only that user’s RMAN metadata

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

********Recovery From Loss Of Datafile For Which No Backup Is Available

> alter tablespace USERS add datafile ‘/PERF/u02/oradata/PERF/users02.dbf’ size 10M;

See what objects are in newly added datafile

> column FILE_NAME format a50

select FILE_ID, FILE_NAME, ONLINE_STATUS, STATUS from dba_data_files;

FILE_ID FILE_NAME

———- ————————————————–

1 /PERF/u02/oradata/PERF/system01.dbf

2 /PERF/u02/oradata/PERF/undotbs01.dbf

3 /PERF/u02/oradata/PERF/sysaux01.dbf

4 /PERF/u02/oradata/PERF/users01.dbf

5 /PERF/u02/oradata/PERF/users02.dbf

Now we remove the recently added file:

$ rm /PERF/u02/oradata/PERF/users02.dbf

> startup mount

> alter database datafile 5 offline;

> alter database open;

> alter database create datafile ‘/PERF/u02/oradata/PERF/users02.dbf’;

> recover datafile ‘/PERF/u02/oradata/PERF/users02.dbf’;

auto

> alter database datafile ‘/PERF/u02/oradata/PERF/users02.dbf’ online;

Done.

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

11.RMAN 11th senario

Hi all,

Got a quick RMAN restore question which seems like it should be a common situation .. but I'm not having much luck finding the answer and the things I've tried seem contradictory...

I'm testing out various RMAN restore/recovery scenarios for 10g R1. Took a full level-0 backup of one of our larger databases (16 tb) and I'm trying to restore the whole thing on another server. While the backup was running (for about a day & a half), a few datafiles were added. The backup completed fine, including "backup current controlfile;". Now my restore is failing with "RMAN-06023: no backup or copy of datafile xxxx found to restore", with reference to the 4 files that were added while the backup was in progress.

So RMAN won't restore the db because it didn't take a backup of these 4 files which were added during the backup. I guess if the datafiles were added after the backup had finished, we'd be fine because they'd be created during recovery with our archive logs (we have all of those).

solution:--

-----------

Briefly: if I tried to restore the database with this method:

sql> startup nomount;

rman> restore database;

... the restore would fail with "RMAN-06023: no backup or copy of datafile xxxx found to restore".

But when I tried it with this sequence:

sql> startup nomount;

rman> restore controlfile;

sql> alter database mount;

rman> restore database;

... it worked! RMAN created the datafiles I needed and proceeded on with the restore.

From the log:

Starting restore at 20-JUN-13

creating datafile fno=4216 name=/uXX/oradata/dbX/datafile_58.dbf

...

channel d1: starting datafile backupset restore

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

...

and so on. I presume the restore will complete successfully now

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

12.Recovering a Database in NOARCHIVELOG Mode Using Incremental Backups - (Current redo lost NOARCHIVELOG Mode)

•You run database trgt in NOARCHIVELOG mode.

•You use a recovery catalog.

•You shut down the database consistently and make a level 0 backup of database trgt to tape on Sunday afternoon.

•You shut down the database consistently and make a level 1 differential incremental backup to tape at 3:00 a.m. on Wednesday and Friday.

•The database has a media failure on Saturday, destroying half of the datafiles as well as the online redo logs.

In this case, you must perform an incomplete media recovery until Friday, the date of the most recent incremental backup. RMAN uses the level 0 Sunday backup as well as the Wednesday and Friday level 1 backups.

Because the online redo logs are lost, you must specify the NOREDO option in the RECOVER command.

After connecting to trgt and the catalog database, recover the database with the following command:

STARTUP FORCE MOUNT;

RESTORE CONTROLFILE; # restore control file from consistent backup

ALTER DATABASE MOUNT;

RESTORE DATABASE; # restore datafiles from consistent backup

RECOVER DATABASE NOREDO; # specify NOREDO because online redo logs are lost

ALTER DATABASE OPEN RESETLOGS;

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

To recover the database with an autobackup of the control file without a recovery catalog: ( Current redo lost - ARCHIVELOG Mode )

Start RMAN and connect to the target database. For example, run:

CONNECT TARGET /

STARTUP NOMOUNT;

SET DBID 676549873;

Restore the autobackup control file, then perform recovery.

In this example, the online redo logs have been lost, and the most recent archived log sequence number is 13243. This example shows how to restore the control file autobackup, then performs recovery of the database to log sequence 13243.

RUN

{

ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; # allocate manually

RESTORE CONTROLFILE FROM AUTOBACKUP

MAXSEQ 100 # start at sequence 100 and count down

MAXDAYS 180; # start at UNTIL TIME and search back 6 months

ALTER DATABASE MOUNT DATABASE;

}

# uses automatic channels configured in restored control file

RESTORE DATABASE UNTIL SEQUENCE 13243;

RECOVER DATABASE UNTIL SEQUENCE 13243; # recovers to latest archived log

If recovery was successful, then open the database and reset the online logs:

ALTER DATABASE OPEN RESETLOGS;

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

Recovery of a Missing Datafile that has no backups (database is open).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist.

Pre requisites: All relevant archived logs.

1. alter tablespace <tablespace_name> offline immediate;

2. alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’;

3. recover tablespace <tablespace_name>;

4. alter tablespace <tablespace_name> online;

If the create datafile command needs to be executed to place the datafile on a location different than the original use:

alter database create datafile ‘/user/oradata/u01/IASDB/newdata01.dbf’ as ‘/user/oradata/u02/IASDB/newdata01.dbf’

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

13.Duplicate a Database Using RMAN in Oracle Database 11g Release 2:

Active Database Duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication, with a few exceptions.

First, and most obviously, you don't need a backup of the source system, but it does have to be in ARCHIVELOG mode.

The passwords in the password files must match for both servers, so remember to set the correct password when creating the password file on the destination server.

Both the source and destination database servers require a "tnsnames.ora" entry for the destination database. In this case I added the following to each server. The destination server still requires the source entry shown in the previous section.

# Added to the tnsnames.ora on source and destination server.

DB11G-DESTINATION =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2)(PORT = 1521))

)

(CONNECT_DATA =

(ORACLE_SID = DB11G)

)

The destination server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration. Remember to restart or reload the listener.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = DB11G.WORLD)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = DB11G)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2.localdomain)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

Create all the required file structure from the destination host. Also, copy pfile to $ORACLE_HOME/dbs on the destination host

When connecting to RMAN, you must use a connect string for both the target and auxiliary connections.

$ ORACLE_SID=DB11G; export ORACLE_SID

$ rman TARGET sys/password@DB11G-SOURCE AUXILIARY sys/password@DB11G-DESTINATION

Include the FROM ACTIVE DATABASE clause in the DUPLICATE command.

DUPLICATE DATABASE TO DB11G

FROM ACTIVE DATABASE

SPFILE

NOFILENAMECHECK;

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

********What is the RMAN backup optimization feature and how does it work?

Answer: The RMAN backup optimization feature is controlled by these commands:

RMAN> configure backup optimization on;

RMAN> configure backup optimization off;

When you turn on backup optimization, all backup commands will skip backups of any file if it has not changed and if it has already been backed up to the allocated device type. A file can be any dbf file, an archived redo log or an RMAN "backup set".

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

********how to speed up rman backup?

-You can enable as follows from the rman prompt;

CONFIGURE BACKUP OPTIMIZATION ON

-U can increase the number of channels used for backup by allocating more channels in the backup script..which is one way of reducing ur backup time..

- You can limit the speed of a backup by using the RATE option of the ALLOCATE CHANNEL or CONFIGURE CHANNEL commands. The RATE option specifies the maximum number of bytes for each second that RMAN reads on the channel.

For example, you can configure automatic channels to limit channel c1 reads to 700 KB a second and channel c2 reads to 1 MB a second:

CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

CONFIGURE DEFAULT DEVICE TYPE TO sbt;

CONFIGURE CHANNEL 1 DEVICE TYPE sbt RATE 700K;

CONFIGURE CHANNEL 2 DEVICE TYPE sbt RATE 1M;

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

********How to restart failed backups in RMAN?

Restartable Backups

Using the restartable backups feature, RMAN can back up only those files that have not been backed up since a specified date. Use this feature after a backup fails to back up the parts of the database missed by the failed backup.

For example, if the largest datafile is less than 10 MB, then you can back up the database daily as follows:

BACKUP DATABASE MAXSETSIZE = 10M;

Then, after a failure you can back up all files in the database that were not backed up in the last 24 hours by issuing:

BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1';

If the SINCE TIME is later than the completion time, then RMAN backs up the file. If you use "BACKUP DATABASE NOT BACKED UP" without the SINCE TIME parameter, then RMAN only backs up files that have never been backed up.

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

*********What is the differenace between backup piece and backup set?

collectgion of backup pieces called backupset.

Backup sets are produced by the RMAN BACKUP command. A backup set usually consists of only one backup piece. RMAN divides the contents of a backup set among multiple backup pieces only if you limit the backup piece size using the MAXPIECESIZE option of the ALLOCATE CHANNEL or CONFIGURE CHANNEL command.

When we can have more than one bk set in each backup? Set the FILEPERSET parameter:

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

*******RMAN NOCATALOG vs CATALOG

RMAN NOCATALOG Your backup information is save into bbdd controlfile, your window is of seven days.

RMAN CATALOG The other option to manage the backup information is the recovery catalog, which is like you have said CATALOG. It should be located in another database rather than the target database. Recovery Catalog uses the target controlfile and synchronizes the repository from there.

If you can have another database, i recommended Catalog.

The RMAN recovery catalog is a schema stored in a database that tracks backups and stores scripts for use in RMAN backup. For most all backup operations, you will want to connect to both the database and the backup recovery catalog, using the "catalog" option.

Advantage of Having Catalog DB is you can reduce the burden on Control File of maintain the info about the Backup.

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

*******Catalog Database crashed

You should be able to restore and recover exactly as normal, working in nocatalog mode (which is the default) using the controlfile repository, as long as the required backups and archivelogs are not older than your controlfile_record_keep_time (default is 7 days).

Then to rebuild the RMAN catalog, create a new one and if you know which directories the backups are in, synchronize them into the new catalog with CATALOG START WITH...

If you use a catalog then your backup informatin stored in 2 places, control file and catalog. Yes, you can restore from autobackup.

But what if autobackup location is gone too? Imagine you have all your rman backups sitting on the tape(netbackup or whatever other tsm ) and no controlfile! How would you restore your database?

The trick is to know the dbid and restore the controlfile from your tape backups

Code:

rman target /

set dbid

startup nomount;

run

{

allocate channel ch1sbt_tape type sbt;

set controlfile autobackup format for device type sbt to

'path_to_controlfile/%F';

restore controlfile from autobackup;

release channel ch1_tape;

}

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

cumulative incremental level1 backup vs differential incremental level1 backup:

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

To recover an individual tablespace when the database is open:

Prepare for recovery as explained in "Preparing to Restore and Recover Database Files".

Take the tablespace to be recovered offline:

The following example takes the users tablespace offline:

RMAN> SQL 'ALTER TABLESPACE users OFFLINE';

Restore and recover the tablespace.

The following RUN command, which you execute at the RMAN prompt, sets a new name for the datafile in the users tablespace:

RUN

{

SET NEWNAME FOR DATAFILE '/disk1/oradata/prod/users01.dbf'

TO '/disk2/users01.dbf';

RESTORE TABLESPACE users;

SWITCH DATAFILE ALL; # update control file with new filenames

RECOVER TABLESPACE users;

}

Bring the tablespace online, as shown in the following example:

RMAN> SQL 'ALTER TABLESPACE users ONLINE';

You can also use RESTORE DATAFILE and RECOVER DATAFILE for recovery at the datafile level.

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

vi generate_datafiles.sql

set head off pages 0 feed off echo off verify off

set lines 200

spool rename_datafiles.lst

select 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' || '/u03/oradata/&1/' || substr(name,instr(name,'/',-1)+1) || ''';' from v$datafile;

spool off

exit;

vi generate_logfiles.sql

set head off pages 0 feed off echo off

spool rename_logfiles.lst

SELECT 'SQL "ALTER DATABASE RENAME FILE '''''|| MEMBER ||'''''' ||chr(10)||'to ''''' || member || '''''" ;' FROM V$LOGFILE;

exit

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

*************To monitor RMAN job progress***************

1.Once a day, we issue the following query through cron to see if

RMAN is still running.

select operation,OBJECT_TYPE,ROW_TYPE from $RMAN_STATUS where status='RUNNING' and OPERATION='BACKUP' and OBJECT_TYPE !='ARCHIVELOG';

2.Script to check the status of running RMAN Backup and Recovery sessions

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'AND OPNAME NOT LIKE '%aggregate%'AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

3.The views V$RMAN_BACKUP_JOB_DETAILS and V$RMAN_BACKUP_SUBJOB_DETAILS provide details about RMAN backup jobs.

4.The V$RMAN_OUTPUT view is an in-memory view and shows the output of all currently running and recently completed RMAN jobs.

The following query shows the output of a RMAN job

SQL> Select OUTPUT from V$RMAN_OUTPUT where SESSION_STAMP='698172913';

5.To monitor job progress:

Before starting the job, create a script file (called, for this example, longops) containing the following SQL statement:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPS

WHERE OPNAME LIKE 'RMAN%'

AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK != 0

AND SOFAR <> TOTALWORK

;

After connecting to the target database and, if desired, the recovery catalog database, start an RMAN job. For example, enter:

RESTORE DATABASE;

While the job is running, start SQL*Plus connected to the target database, and execute the longops script to check the progress of the RMAN job. If you repeat the query while the restore progresses, then you see output such as the following:

SQL> @longops

SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE

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

8 19 1 10377 36617 28.34

SQL> @longops

SID SERIAL# CONTEXT SOFAR TOTALWORK % COMPLETE

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

8 19 1 21513 36617 58.75

SQL> @longops

SID SERIAL# CONTEXT SOFAR TOTALWORK % COMPLETE

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

8 19 1 29641 36617 80.95

SQL> @longops

SID SERIAL# CONTEXT SOFAR TOTALWORK % COMPLETE

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

8 19 1 35849 36617 97.9

SQL> @longops

no rows selected

***************Script – Check RMAN Backup Status **************************

Scripts to check backup status and timings of database backups -

This script will be run in the database, not the catalog.

*********This script will report on all backups – full, incremental and archivelog backups -

col STATUS format a9

col hrs format 999.99

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

*********This script will report all on full and incremental backups, not archivelog backups -

col STATUS format a9

col hrs format 999.99

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

where input_type='DB INCR'

order by session_key;

Bollipelly PruthviRaj Goud

Saturday, 4 February 2023

Oracle Database 11g: New Features in RMAN :

0 Comments:

Post a Comment

About Me

Previous Posts