Feeds:
Posts
Comments

Archive for the ‘RMAN’ Category

I am posting today to the honor of my friend’s  mother or father who had suffer and is suffering from CANCER. May god treat there disease soon and make them healthy and well .

Few days Ago one of my friend ask the What is RMAN incremental backup, when to use it Question. So, today I am talking about that.

Below are some RMAN recovery scenario for real time.

 RMAN incremental backups back up only datafile blocks that have changed since a specified previous backup. You can make incremental backups of databases, individual tablespaces or datafiles.

The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.
Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are not acceptable. Therefore , doing incremental backups in RMAN is an ideal alternative. Always follow the backup strategy according to an acceptable MTTR (mean time to recover) in your environment.

Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

A level 1 incremental backup can be either of the following types:

  • differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
  • cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Differential Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Description of Figure 4-1 follows

In the example shown in Figure 1, the following occurs:

  • Sunday An incremental level 0 backup backs up all blocks that have ever been in use in this database.
  • Monday – SaturdayOn each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.
  • The cycle is repeated for the next week.

Cumulative Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Description of Figure 4-2 follows

In the example shown in Figure 2, the following occurs:

  • SundayAn incremental level 0 backup backs up all blocks that have ever been in use in this database.
  • Monday – SaturdayA cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.
  • The cycle is repeated for the next week.

RMAN command to create level 0 backup which is needed before running of incremental backup level 1
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN command to run level 1 backup. Level 1 backup will backup all blocks changed since most recent cumulative or differential backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN command to run level 1 cumulative backup. Level 1 backup will backup all blocks changed since most recent Level 0 backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN command to backup database level 1 and skip datafiles and archived redo logs that cannot be read due to I/O errors to be excluded from backup
RMAN> BACKUP INCREMENTAL LEVEL 1 INACCESSIBLE DATABASE;


 

There are couple of ways to determine if RMAN database is registered with a catalog.

Using RMAN when you connect to RMAN catalog and try to run a RMAN command like “list backup” it will generate an error as shown below
$ rman target / catalog rmancataloguser/rmancatalogpassword@catalogdb

Recovery Manager: Release 10.2.0.4.0 – Production on Sun Jul 17 08:33:42 2011

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

connected to target database: TEST01 (DBID=1023910334)
connected to recovery catalog database

RMAN> list backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/17/2011 08:33:48
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Another way would be to connect to the catalog schema through SQL*Plus and check view RC_DATABASE
$ sqlplus rmancataloguser/rmancatalogpassword@catalogdb
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jul 17 08:45:58 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 32bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from rc_database where name = ‘TEST01’;

no rows selected


 

Remember that only SPFILE or control file can be restored from autobackup

Scenario when all control files for some reason have been lost then how to restore.

1) Try to shutdown database which will fail as the control file(s) doesn’t exists so we needs to perform shutdown abort
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/apps/oracle/oradata/TEST01/control1.ora’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

2) Start database in nomount to restore the controlfile as controlfile is missing so database can only be started in mount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

3) Start rman and connect to target database and set a past DBID
$ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Dec 7 21:08:53 2010

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

RMAN> connect target /

connected to target database: TEST (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

4) Restore controlfile from autobackup
RMAN> restore controlfile from autobackup;

Starting restore at 16-NOV-13
using channel ORA_DISK_1

..
Finished restore at 16-NOV-13

5) Open database in mount
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Later if you got RMAN-06189

RMAN-06189 current DBID number does not match target mounted database (number)
Cause SET DBID was used to set a DBID that does not match the DBID of the database to which RMAN is connected.
Action If the current operation is a restore to copy the database, do not mount the database. Otherwise, avoid using the SET DBID command, or restart RMAN.

 

6) Recover database
RMAN> recover database;

Starting recover at 16-NOV-13

….

..

media recovery complete, elapsed time: 00:00:02
Finished recover at 16-NOV-13

 7) Database has to be open with resetlogs

Why open resetlogs, or what is the use of open resetlogs after a incomplete recovery?

because its

1. Archives the current online redo logs.

2. Clears the contents of the online redo logs, and resets the online redo logs to log sequence 1
RMAN> alter database open resetlogs;

database opened

Restore spfile through RMAN, here are some scenerios/examples.

1. In this scenerio there is a autobackup is present of spfile, database is in no mount state so to use AUTOBACKUP, DBID needs to be set before restoring spfile.
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010

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

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 16-NOV-13
using target database control file instead of recovery catalog

..

channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-NOV-13

2. In this scenerio there spfile is restored from autobackup, database is in nomount state and if the filename of backup piece is known so it can be passed to restore spfile
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Nov 29 17:45:04 2010

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

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> restore spfile from ‘/apps/oracle/product/10.2.0/db_1/dbs/backup_piece_name’;
– RMAN> restore spfile from ‘backup_piece_name’;

Starting restore at 16-NOV-13
using channel ORA_DISK_1

….

Finished restore at 28-NOV-10

 

Scenerio where there is no autobackup of control file so Oracle goes to last 7 days by default and does not find it

If you want to tell RMAN to look for spfile more than 7 days so using maxdays RMAN will look for spfile from current date to currentday – maxdays

“RMAN> restore spfile from autobackup maxdays 200;”
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010

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

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 16-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20131115
channel ORA_DISK_1: looking for autobackup on day: 20131114
channel ORA_DISK_1: looking for autobackup on day: 20131113
channel ORA_DISK_1: looking for autobackup on day: 20131112
channel ORA_DISK_1: looking for autobackup on day: 20131111
channel ORA_DISK_1: looking for autobackup on day: 20131110
channel ORA_DISK_1: looking for autobackup on day: 20131109
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/16/2013 15:08:30
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

 

When AUTOBACKUP is turned on Oracle will create backup of controlfile and spfile. When one runs backup of the database or archive log, changes in tablespace occur like creating a new tablespace, add a new datafile and dropping tablespace Oracle create a separate backup piece for the control file and spfile.

Shows how to turn on CONTROLFILE AUTOBACKUP
RMAN> connect target /

connected to target database: TEST (DBID=1992878807)

RMAN> configure CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Below is the code to turn on controlfile autobackup through PL/SQL and setting the format.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘CONTROLFILE AUTOBACKUP’,’ON’);
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE’,’DISK TO ”T_%U”’);

Steps if you need to restore all datafiles in a tablespace is corrupted so you would need to restore the table space. The following example shows the steps

  1. Tablespace cannot be made offline as it will try to flush all blocks in the datafiles but as the datafile is corrupted.

SQL> alter tablespace ts_something offline;
alter tablespace newton offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/apps/oracle/oradata/TEST01/newton01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

  •  So using table space offline immediate Oracle will not check if the file exists and does not perform checkpoint and we need to do media recovery on the table space

SQL> alter tablespace newton offline immediate;

Tablespace altered.

  •  Display datafile status

SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = ‘NEWTON’
and t.ts# = d.ts#;

NAME STATUS
—————————————————————— ——-
/apps/oracle/oradata/TEST01/newton01.dbf RECOVER

  •  restore the table space

RMAN> run {
2> restore tablespace NEWTON;
3> }

Starting restore at 15-NOV-13
using channel ORA_DISK_1

….

..
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 15-NOV-13

  • table space has be restored but not recovered so shows it cannot be made online till table space is recovered

SQL> alter tablespace ts_something online;
alter tablespace ts_something online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/apps/oracle/oradata/TEST01/newton01.dbf’

  •  recover table space

SQL> recover tablespace ts_something;
Media recovery complete.

  •  Tablespace can be made online

SQL> alter tablespace ts_something online;

Tablespace altered.

  • Shows data file status

SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = ‘NEWTON’
and t.ts# = d.ts#;

NAME STATUS
————————————————————————————————————————————
/apps/oracle/oradata/TEST01/newton01.dbf ONLINE


 

Using the steps below one take cold backup using RMAN. As it’s a cold backup the database as the database is in mount stage and the database doesn’t have to be archivelog mode .

Step 1) Shutdown database

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

Step 2) Start database in mount stage

SQL> startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 75497480 bytes
Database Buffers 88080384 bytes
Redo Buffers 2174976 bytes

Step 3) Run rman and connect to target database and run rman to backup database and connection to catalog if you are using one
$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Apr 23 02:33:38 2010

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

connected to target database: TEST01 (DBID=1992878807, not open)

RMAN> backup database;

 

To find RMAN catalog version log-in to the catalog through SQL*Plus and query the table rcver will print version.

SQL> select * from rcver;

VERSION
————
10.02.00.00

SQL> select object_type from user_objects where object_name = ‘RCVER’;

OBJECT_TYPE
——————-
TABLE

SQL> desc rcver;
Name Null? Type
—————————————– ——– —————————-
VERSION NOT NULL VARCHAR2(12)

Hope it’s help. If you guys do have any recover or restore scenario you can post you comments about that.

Thanks & Regards

Nimai Karmakar

Read Full Post »

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have

ursvenkat's Blog

Oracle Applications DBA Blog

The Ivica Arsov Blog

Database Management & Performance

gumpx

DBA's online diary