NAVEEN

Friday, March 5, 2010

Learning RMAN

Implementation of

Rman (Recovery Manager)









To get the full benefits of Rman we should recovery catalog.



Recovery Catalog



The recovery catalog is an optional repository of information about your target databases that RMAN uses and maintains.



Physical location of the catalog



We should place the catalog database on a different server than the target database. If we fail to do this, we jeopardize backup and recovery options, because we make it possible to lose both the catalog and the target database.



The catalog database should be created with the latest version of Oracle in your production environment. This helps to minimize compatibility issues and complexity when you start to back up your target databases.





Creating a catalog



The examples in this section provide details for creating a catalog database and registering a target database within the catalog. These examples assume that your catalog database is on a different host than your target database.



Catalog database: Oracle 9.2.0.4 (GEK1) on gecko

Target database: Oracle 10.1.0.2 (AKI1) on akira



To create a recovery catalog follow these steps



1. Create a specific tablespace to hold the catalog objects.

2. Create a catalog schema.

3. Issue appropriate grants

4. Create the schema objects.



Oracle@akira:~> sqlplus system/manager@GEK1



CREATE TABLESPACE rman_cat

DATAFILE ‘/U01/oracle/db/GEK1/CAT/rman_cat_01.dbf’

SIZE 50M;



Now that we have a tablespace to store our schema objects, we can create the schema



CREATE USER rmancat

IDENTIFIED BY rmancat

DEFAULT TABLESPACE rman_cat

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON rman_cat;



Before we create the catalog objects, we need to grant special privileges to new schema. These privileges, granted through the RECOVRY_CATALOG_OWNER role, let the schema manage its catalog objects.



GRANT RECOVERY_CATALOG_OWNER TO rmancat;

GRANT CREATE TYPE TO rmancat;



We can now create the catalog objects within our new schema. In order to perform this step, invoke RMAN, connect to newly created catalog schema, and issue the create catalog command. If we don't specify a tablespace with the create catalog command, the catalog objects are created in the default tablespace assigned to the catalog owner.



Oracle@akira:~> rman catalog rmancat/rmancat@GEK1



Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995,2004, Oracle. All rights reserved.

connected to recovery catalog database

recovery catalog is not installed



RMAN> create catalog;

Recovery catalog created



RMAN> exit;



At this point, we now have an operational RMAN catalog



Registering a target database



After creating a catalog, the next logical step is to register a target database. We won’t be able to backup the target with the catalog unless the target database is registered.



Invoke RMAN, connect to both the target and the catalog and issue the register database command.



Oracle@akira:~> rman target / catalog rmancat@rmancat@GEK1



RMAN> Register Database;



RMAN> Exit;









Configuring the RMAN Environment



Configure command



We can configure persistent settings in the Rman environment. The configuration setting is done once, and used by Rman to perform all subsequent operations.



To display the pre configured settings type the command SHOW ALL



RMAN> SHOW ALL



There are various parameters that can be used to configure RMAN operations to suit our needs.



Some of the things that we can configure are



1. Required number of backups for each datafile.

2. Number of server processes that will do backup/restore operations in parallel.

3. Directory where on disk backups will be stored.

Etc.,



We can return any CONFIGURE command to it’s default setting by running the command with the CLEAR option.



$ rman target / catalog rmancat/rmancat@GEK1



RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;



RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;



RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;



RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT

'/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';



RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;



RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO

'/u01/oracle/db/AKI1/bck/ora_cf%F';



RMAN> CONFIGURE BACKUP OPTIMIZATION ON;



RMAN> SHOW ALL;









Working with RMAN



There are few things we need to have in place before instructing RMAN to connect to the target.



1. Appropriate target environment variables must be established.

2. We must have access to an O/S account or a schema that has SYSDBA privilege.



Before you connect to your target database, you must ensure that the standard Unix environment variables are established. These variables include: ORACLE_SID, ORACLE_HOME, PATH, NLS_LANG, and NLS_DATE_FORMAT. They govern the name of the instance, the path to the RMAN executable; and the behavior of backup, restore, and reporting commands.



When using RMAN, NLS_LANG should be set to the character set that your database was created with. If you do not set NLS_LANG, you may encounter problems when issuing BACKUP, RESTORE, and RECOVER commands.



Once you have the appropriate environment variables set, you then need access to an O/S account or a database schema that has SYSDBA privileges. You must have access to the SYSDBA privilege before you can connect to the target database using RMAN. There are two methods of administering the SYSDBA privilege:



1. Locally via O/S authentication

2. Remotely via password file



For local connections, RMAN automatically connects you to the target database with SYSDBA privileges.



Setting up a password file is the other method by which we can administer the SYSDBA privilege. There are two good reasons to use RMAN with a password file.



1. Oracle has deprecated the use of CONNECT INTERNAL and Server Manager.

2. We may want to administer RMAN remotely through a network connection.





For example, if you're in an environment where you want to back up all of your target databases from one place and not has to log on to each host and back up the database, you must do it via a network connection. To remotely administer RMAN through a network connection, you need to do the following:



• Create a password file

• Enable remote logins for password file users











Create a password file for Target



To create the password file, as the Oracle software owner or as a member of the dba group.



$ cd $oracle_home/dbs

$ orapwd file=sidname password=password entries=n



There are three user-provide variables in this example



1. sidname : The SID of the target instance

2. password : The password to be used when we connect a user SYS with SYSDBA privilege.

3. n : The maximum number of schemas allowed in the password files.



Example



$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwAKI1 password=goofi entries=30



After we create a password file, we need to enable remote logins. To do this, set the instance’s REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.



Setting this parameter to exclusive signifies that only one database can use the password file and that users other than sys and internal can reside in it. We can now use a network connection to connect to your target database as SYSDBA.



Test the connection, try to connect from a PC to the remote database as SYS with SYSDBA privileges:



$ sqlplus "sys/goofi@AKI1 as sysdba"



Note that we have to create a password file only for the target database and not for the catalog. This is because when you connect to the target, you need to connect as an account that has the SYSDBA privilege. When you connect remotely to a target database, the SYSDBA privilege is enabled through the password file. This is unlike a connection to the catalog, for which SYSDBA is not required, because you log in as the owner of the catalog schema.



When the SYSDBA privilege is granted to a specified user, the user can be queried in the V$PWFILE_USERS view.



SQL> GRANT SYSDBA TO rmancat;

SQL> select * from v$pwfile_users where username='RMANADMIN';

Invoking the RMAN Executable





In order to use Rman we have to invoke the executable. Once we have invoked the executable, we get an RMAN prompt, from which we can execute RMAN commands.



The executable of RMAN is located with all of the other oracle executables, in the BIN directory of oracle installation.



From O/S command prompt issue the command RMAN



$ rman



Connecting to target with no catalog



O/S Authentication



$ rman target / nocatalog



We can use O/S authentication only from an O/S account on the database server



Password file authentication



client-pc> rman target sys/goofi@AKI1 nocatalog



Hiding the password



Connect to the database after RMAN has been invoked prevents any password information from showing up in a process list.



SQLPLUS> $ rman nocatalog

RMAN> connect target sys/pwd@SID





Connecting to both Target and Catalog



If we are using catalog, we will typically connect to the target and the catalog at the same time. This is because when we are performing backup and recovery operations both the target and the catalog need to be aware of your activities.



O/S authentication



$ rman target / catalog rmancat/rmancat@GEK1



This connects us to the target and catalog database at the same time. Alternatively we can invoke RMAN first and then issue connect commands for the target and catalog separately.



$ rman

RMAN> connect catalog rmancat/rmancat@GEK1

RMAN> connect target /



Password Authentication



client-pc> rman target sys/goofi@AKI1 catalog rmancat/rmancat@GEK1





BACKUPS



RMAN can backup data files, control files, archived redo files, and backup pieces. RMAN doesn’t backup the online redo logs.













RMAN, as part of its default backup behavior, touches each datafile block, performs verification checks, and then logs any detected corruption. You can monitor potential corruption by querying the V$BACKUP_CORRUPTION and V$COPY_CORRUPTION views.



For a database to be backed up it must be in mount or open mode. The database needs to at least be in mount mode, because RMAN needs to access the target database control file before performing a backup.



Full Database Offline Backup





For offline backups, the database needs to be shutdown and restarted in mount mode. The database doesn’t have to be in archievelog mode.



Here's a shell script for an Oracle9/10 database that shuts down the database, mounts it, backs it up, and opens it:



#!/bin/bash



rman target / catalog rmancat/rmancat@GEK1 <
shutdown immediate;

startup mount;

backup database format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';

alter database open;

EOF

exit



$ ./rman_offline.bash



The following files are created:

$ cd /u01/oracle/db/AKI1/bck>

$ ls -l

-rw-r----- 1 oracle dba 7012352 ora_cfc-1994497080-20040927-08

-rw-r----- 1 oracle dba 349777920 ora_df537975652_s39_s1

-rw-r----- 1 oracle dba 138788864 ora_df537975652_s40_s1



Full Database Online Backup



#!/bin/bash



rman target / catalog rmancat/rmancat@GEK1 <
backup database format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';

EOF

exit



Unlike traditional online (hot) backups, RMAN does not put tablespaces in backup mode. No extra redo is generated. For high-transaction databases, this can lead to significant resource savings.



Again, note that with Oracle8i, the syntax is a bit different with respect to the run{} command:



#!/bin/bash



rman target / catalog rmancat/rmancat@GEK1 <
run {

allocate channel d1 type disk;

backup database format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';

}

EOF

exit





Backing up a Tablespace



The ability to specify only a subset of tablespaces in a backup operation can add flexibility to your backup strategy. For Oracle9i, the syntax can be fairly simple:

RMAN> backup tablespace system format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';

Once again, note that with Oracle8i, the syntax is a bit different with respect to the run{} command:

run {

allocate channel d1 type disk;

backup tablespace system, users include current controlfile

format '/u01/oracle/db/AKI1/bck/ora_df%t_s%s_s%p';

}



A nice feature of Oracle9i is its ability to configure the control file to be backed up automatically whenever you issue a backup command. You can do this by using the configure command as follows:



Reset to Defaults



RMAN>

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;



Set CONTROLFILE AUTOBACKUP on:



RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;



RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE

TYPE DISK TO '/u01/oracle/db/AKI1/bck/ora_cf%F.con';



Now backup a tablespace and watch the logging output for the saved controlfile



RMAN> backup tablespace users format '/u01/oracle/db/AKI1/bck/rman_%s_%t_%d.bck';



With Oracle9i, the syntax for backing up archived redo log files is fairly simple:



RMAN> backup archivelog all;

Another Oracle9i technique is to use the backup command's plus archivelog clause to include the archive redo log files as part of a backup. This creates at least two backup pieces, one for the datafiles, and one for the archived redo log files:



RMAN> backup database plus archivelog;

Incremental Backups



One of the most impressive features of RMAN is its ability to perform incremental backups.



RMAN has the ability to detect which blocks in a datafile has changed since the last backup and will copy only those modified blocks



When RMAN copies only the modified blocks during a backup, this is called compression.



Skipping unmodified blocks gives RMAN a big advantage over file-based backups in that the resources required to do a backup or restore can be considerably less. Additionally, this means that the backup time and tape/disk space required correlates to the number of changes made to the database, and not necessarily to the size of the database. For very large databases, this alone can necessitate the use of RMAN.



Much of the nomenclature around incremental backups contains the term level. The basic idea behind RMAN level-based backups is to back up only blocks that have been modified since the previous backup. Incremental backups can be applied to the database, tablespaces, or datafiles. Oracle refers to this usage of levels as a multilevel incremental backup. These levels can range from level 0 to a maximum of level 4.



There are two flavors of incremental backups -- differential and cumulative. A differential incremental backup tells RMAN to back up blocks that have changed since level n or lower. For example, if you take a level 1 differential backup, you will back up blocks that have changed since the previous level 1 backup. Differential backups are the default incremental backup mode.



If you take an incremental backup higher than level 0, and no prior level 0 exists, RMAN automatically creates a level 0 backup.



A cumulative incremental backup instructs RMAN to back up blocks that have changed since level n-1 or lower. For example, if you take a level 1 cumulative backup, RMAN will back up blocks that have changed since the most recent level 0 backup.



A full backup backs up the exact same blocks as a level 0. The difference between a full backup and a level backup is that a full backup is not known to any subsequent incremental backups. Therefore, they cannot be used as a basis when applying incremental backups during a recovery operation. A full backup is the default backup type if no incremental level is specified.



Why all the choices? A differential backup takes less space and time to perform but requires more time to restore. It follows that a cumulative backup takes more space and time to perform but less time to restore. So it becomes a tradeoff issue; do you want to minimize your backup time or minimize your restore time? We prefer to minimize our restore time, and therefore, we use cumulative backups. For small databases, we recommend daily RMAN level 0 backups.



RMAN> backup incremental level 0 database;



Here's an Oracle9i cumulative backup example in which you tell RMAN to back up all blocks that have been modified since the most recent level 3 or lower backup.



RMAN> backup incremental level 4 cumulative database;



Validating backups



One nice feature of RMAN is the ability to report on the status of backups. The simplest way to view backup information is via the list backup command.

View Backup Information



RMAN> list backup;



Is Backup restorable ?



RMAN also has a way to verify whether the backup files are restorable. You accomplish this by issuing the restore database validate command. This command doesn't actually restore any datafiles, it just validates that the contents of the backup sets can be restored if necessary. The following example works with both Oracle8i and Oracle9i:

RMAN> run {

allocate channel d1 type disk;

restore database validate;

}



Validate Backup



You can run a test RMAN backup that does not generate any output. The test checks datafiles for physical and logical corruption and that all database files exist and is in the correct locations. For example:

RMAN> backup validate database archivelog all;



In general there are three steps involved in restoring files:



1. Ensure that the target database is started in the appropriate mode for the restoration operation. For lost control files, this will be nomount mode. If the entire database needs to be restored, this will be mount mode. If datafiles that don't belong to the SYSTEM tablespace are damaged, you have the option of keeping the database open and taking only the tablespace(s)/datafile(s) that needs to be restored offline.

2. Start RMAN and connect to the target and recovery catalog if one is being used.

3. Run the appropriate RMAN RESTORE command to bring back required files. The requested files and the appropriate archived redo log files will be restored.







Restoring and Recovering All Datafiles



Our first step is to make sure that the target database is shutdown



SQL> connect sys/... as SYSDBA;

SQL> shutdown abort;

ORACLE instance shut down.



Next, you need to start up your target database in mount mode. RMAN cannot restore datafiles unless the database is at least in mount mode, because RMAN needs to be able to access the control file to determine which backup sets are necessary to recover the database. If the control file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in the following example to mount the database:



SQL> startup mount;

Oracle instance started.



Since backup set files are created in an RMAN-specific format, you must use RMAN to restore the datafiles. To use RMAN, connect to the target database:



$ rman target / catalog rmancat/rmancat@GEK1



The remainder of this example shows how to restore all of the datafiles of the target database. RMAN will go to its last good backup set and restore the datafiles to the state they were in when that backup set was created.



When restoring database files with Oracle9i, RMAN reads the datafile header and makes the determination as to whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command.



With Oracle9i, you don't need to allocate a channel explicitly. Instead, you can use the default channel mode:



RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;



For Oracle8i, the ALLOCATE, RESTORE, and RECOVER commands need to be enclosed by the run{} command:



RMAN> run {

allocate channel d1 type disk;

restore database;

recover database;

}

alter database open;



Or alternatively, once RMAN has restored the datafiles, you can use SQL*Plus to recover the database and open it for use:

$ sqlplus /nolog

SQL> connect sys/... as SYSDBA;

SQL> recover database;

SQL> alter database open;



Restoring Specific Tablespaces/Datafiles



In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;



Take the tablespace/datafile that needs recovery offline, restore the tablespace/datafile, recover the tablespace/datafile, and bring the tablespace/datafile online. If you cannot take the tablespace/datafile offline, then shutdown abort the database and restore in mount mode.



First try to take the Tablespace offline;



sqlplus "sys/managase as sysdba"

SQL> alter tablespace tab offline;

$ rman target / catalog rmancat/rmancat@GEK1

RMAN> restore tablespace tab;

RMAN> recover tablespace tab;

SQL> alter tablespace tab open;



SQL> connect sys/... as SYSDBA;

SQL> shutdown abort;

SQL> startup mount;

$ rman target / catalog rmancat/rmancat@GEK1

RMAN> restore tablespace tab;

RMAN> recover tablespace tab;

SQL> alter database open;



Instead of a tablespace, you can restore and recover a specific datafile:



RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'

RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'



Restoring Control Files

In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;



In an ideal world you'll never use RMAN to restore a control file. But if something catastrophic happens, and you lose all control files, here are the steps for getting them back:





SQL> connect sys/... as SYSDBA;

SQL> shutdown abort;

SQL> startup nomount;

$ rman target / catalog rmancat/rmancat@GEK1

RMAN> restore controlfile;

RMAN> alter database mount;

RMAN> alter database open;



If this fails with ...



RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 09/28/2004 10:31:52

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



... then you must perform a recover database:



SQL> shutdown abort;

SQL> startup mount;

$ rman target / catalog rmancat/rmancat@GEK1

RMAN> recover database;

RMAN> alter database open resetlogs;



Note, that all offline archivelogs are now useless, perform a full back as soon as possible.



In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;



sqlplus "sys/manager as sysdba"

SQL> shutdown abort;

SQL> startup nomount;

$ rman target / catalog rmancat/rmancat@GEK1

RMAN> restore controlfile;

RMAN> alter database mount;

RMAN> restore database;

RMAM> recover database;



RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 09/28/2004 11:03:23

RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 8448414

Since the online logs were lost, complete recovery is not possible. Open the database with resetlogs to continue.

RMAN> alter database open resetlogs;

Note, that all offline archivelogs are now useless, perform a full back as soon as possible.



Time-Based or Change-Based Incomplete Recovery



Incomplete recovery uses a backup to produce a noncurrent version of the database. In other words, you

do not apply all of the redo records generated after the most recent backup.



You usually perform incomplete recovery of the whole database in the following situations:



• Media failure destroys some or all of the online redo logs.

• A user error causes data loss, for example, a user inadvertently drops a table.

• You cannot perform complete recovery because an archived redo log is missing.

• You lose your current control file and must use a backup control file to open the database.



To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database; in other words, a database with a new stream of log sequence numbers starting with log sequence 1.





sqlplus "sys/manager as sysdba"

SQL> shutdown abort;

SQL> startup mount;

$ rman target / catalog rmancat/rmancat@GEK1

RMAN> restore database;

SQL> recover database until time '2004-09-29:10:35:00';

media recovery complete.

SQL> alter database open resetlogs;



Maintenance Commands

RMAN has a few maintenance commands, shown next.

RMAN> report need backup; What files require a backup now

RMAN> crosscheck backup; Determines whether a backup set and its related pieces still exist on media. If a backup piece exists in the location recorded in the control file of the target database or in the optional recovery catalog, its status is marked as AVAILABLE. If it is not at the specified location, it is marked as EXPIRED.

RMAN> delete expired backup of database;

RMAN> delete backup of database; For Oracle9i, this command deletes the physical files associated with backup sets and datafile copies, updates their status in the control file, and removes their information from the optional recovery catalog (if one is used).

In Oracle8i and Oracle9i, backups are flagged as EXPIRED if they cannot be found at their recorded location. Deletion of EXPIRED backups removes their information from the control file and from the optional recovery catalog (if one is used).

RMAN> create catalog;

RMAN> drop catalog; Create Recovery Catalog.

Drops all objects associated with the recovery catalog schema.

RMAN> report need backup days 2 database;







RMAN> report need backup days 2

tablespace system;



RMAN> report obsolete;

RMAN> report unrecoverable; To report on those datafiles that, if restored, would require application of two days (or more) worth of archived redo log files:



To generate the same report but only for SYSTEM tablespace datafiles:



To list backups considered obsolete

Reports on all unrecoverable datafiles





Complete Recovery using RMAN

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

Case :

------

1. Take a full database backup. Shutdown the database and delete one datafile data1.dbf



2. Startup the database you will get an error.



3. Recover the datafile









C:\> rman target /



RMAN> run{

Allocate channel c1 type disk

format 'c:\sql\backup%U.bak';

Backup database;}



Configuring RMAN and taking backup

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





RMAN> show all;



RMAN> configure channel device type disk format

'c:\sql\backup\name%U.bak';



RMAN> configure controlfile autobackup on;



RMAN> configure retention policy to recovery window of 7 days;



RMAN> configure datafile backup copies for device type disk to 2;



RMAN> backup datafile 'c:\sql\data\ora1.dbf;



RMAN> backup copies 2 datafile 2 format 'c:\%U','d:\%U';



RMAN> configure backup optimization on;



RMAN> backup database;(will take full backup)



RMAN> backup database;(will not take any backup since optimization is on, so try without optimization)



RMAN> report need redundancy 3;



RMAN> list backup;



RMAN> report need backup redundancy 7;





Using recover tablespace command :

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

Case:

-----

1. Create a tablespace using OMF and create a table in the same tablespace



2. create tablespace good datafile size 1M;



3. create table one ( a number);



4. insert into one values (1111);



5. commit;



Now, shutdown the database and delete the data file of the tablespace "good" using OS command. Startup the database in nomount stage. You will find error.





From another session, run the following and after the recovery check for the tablespace and table



RMAN> run{

sql "alter database datafile 3 offline";

sql "alter database open";

sql "alter tablespace good offline immediate";

restore tablespace good;

recover tablespace good;

sql "alter tablespace good online";

}





Incomplete Recovery (RMAN) - Until Time

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

Case:

-----

1. Create a table in one of your tablespace.

2. Force log switch.

3. Take backup using RMAN

4. Drop table and note down the drop time.



Steps:

------

1. create table new (n number) tablespace good;



2. insert into new values (1111);



3. commit;



4. alter system switch logfile;



5. alter system switch logfile;



6. alter system switch logfile;





RMAN> backup database;



RMAN> drop table new;



RMAN> alter system switch logfile;



RMAN> alter system switch logfile;



RMAN> alter system switch logfile;



Now, shutdown the database and startup the database in mount stage. Recover the database until time. Time should be just before the table was dropped.



RMAN> sql "ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MON-YYYY HH24:MI:SS''";



RMAN> run{

set until time '26-DEC-2005 16:02:00';

restore database;

recover database;

sql "alter database open resetlogs";

}





Until Sequence:

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



Take backukp of the whole database



RMAN> backup database;



(From another session create a table and force log switch)



RMAN> create table one (a number);



RMAN> alter system switch logfile;



RMAN> alter system switch logfile;



Shutdown the database, delete one archive log file and datafile(non system)

Startup the database



Steps:

------

1. Startup



From RMAN session recover the database



RMAN> run{

set until sequence 11 thread 1;

restore database;

recover database;

sql "alter database open resetlogs";

}









RMAN different ways of taking backup

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



RMAN> run {

allocate channel c1 type disk;

backup format 'c:\sql\back%U.bak'

database; }



RMAN> run{

allocate channel c1 type disk

format 'c:\sql\backup%U.bak

backup datafile 'c:\sql\data\one.dbf';}



RMAN> run{

allocate channel c1 type disk

format 'c:\sql\backup%U.bak

backup (datafile 'c:\sql\data\cat.dbf)

(datafile 'c:\sql\data\one.dbf')

}



RMAN> run{

allocate channel c1 type disk

format 'c:\sql\backup%U.bak

backup (datafile 'c:\sql\data\cat.dbf',

'c:\sql\data\good.dbf');

}





RMAN> run{

allocate channel c1 type disk

format 'c:\sql\backup%U.bak

backup (datafile 'c:\sql\data\cat.dbf',

'c:\sql\data\good.dbf' include current controlfile);

}



RMAN> run{

allocate channel c1 type disk

backup

incremental level=0

format 'c:\sql\backup%U.bak

(datafile 'c:\sql\data\good.dbf' tag=good);

}



RMAN> run{

allocate channel c1 type disk

allocate channel c2 type disk

backup

incremental level=0

format 'c:\sql\back%U.bak'

(datafile 1 channel c1 tag=good)

(datafile 2,3 channel c2 tag=2_good);

}



RMAN> run{

allocate channel c1 type disk;

backup

incremental level=0

format 'c:\sql\backup%U.bak'

(database filesperset=2 include current controlfile);

}



RMAN> run{

allocate channel c1 type disk;

backup

format 'c:\sql\backup%U.bak'

(archivelog from logseq=200 until logseq=221

thread=1 delete input);

}



RMAN> run{

allocate channel c1 type disk;

copy

datafile 'c:\sql\data\good.dbf' to 'd:\bak\good.dbf'

datafile 'c:\sql\data\good1.dbf' to 'd:\bak\good1.dbf'

datafile 'c:\sql\data\system.dbf' to 'd:\bak\system.dbf';

}



RMAN> run{

allocate channel c1 type disk

copy

datafile 'c:\sql\data\good.dbf' to 'd:\bak\good.dbf'

datafile 'c:\sql\data\good.dbf' to 'd:\bak\good.dbf'

archivelog 'c:\sql\archive\arch1.bak' to 'd:\bak\200.bak');

}



RMAN> run{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup

incremental level=0

format 'c:\sql\bak\%U.bak'

database;

}



Restore the datafile to a new location

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

Shutdown the database and delete one datafile and startup

Using RMAN restore to different location and recover



RMAN> run{

set newname for datafile 5 to

'c:\sql\ASK\data\new.dbf';

restore database;

switch datafile all;

recover database;

alter database open;

}





3 comments:

  1. Hi Naveen. I do have read about this recovery technique from so many other blogs but this one is the best of all. You have explained the whole recovery process by taking all the scenarios and step by step. I got an opportunity to learn so many new facts that I was not aware of.
    upgrade sap 6.0

    ReplyDelete
  2. I like your blog and this is very informative about learning RMAN. Thanks for sharing!!!!!!
    We are offering 1-month free trial of backup on cloud and assuring the lowest price guarantee. Please contact us: +91-9971329945
    Visit Here-
    backup on cloud
    Web Hosting
    backup storage

    ReplyDelete