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 (AIRTEL) on sonu
Target database: Oracle 10.1.0.2 (AIRTEL1) on sonu1
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@sonu:~> sqlplus system/manager@AIRTEL
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@sonu:~> rman catalog rmancat/rmancat@AIRTEL
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@sonu:~> rman target / catalog rmancat@rmancat@AIRTEL
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@AIRTEL
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/Airtel1@sonu1 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/Airtel1@sonu1 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@sonu1
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 (AIRTEL) on sonu
Target database: Oracle 10.1.0.2 (AIRTEL1) on sonu1
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@sonu:~> sqlplus system/manager@AIRTEL
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@sonu:~> rman catalog rmancat/rmancat@AIRTEL
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@sonu:~> rman target / catalog rmancat@rmancat@AIRTEL
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@AIRTEL
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/Airtel1@sonu1 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/Airtel1@sonu1 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@sonu1
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@sonu1
RMAN> connect target /
Password Authentication
client-pc> rman target sys/Airtel@sonu1 catalog rmancat/rmancat@sonu1
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@sonu1 <<eof
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/sonu1/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 <<eof
backup database format '/u01/oracle/db/sonu1/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@sonu1 <<eof
run {
allocate channel d1 type disk;
backup database format '/u01/oracle/db/sonu1/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/sonu1/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/sonu1/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/sonu1/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/sonu1/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.
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.
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@sonu1
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;
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@sonu1
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@sonu1
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@sonu1
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/sonu1/tab/sonu1_tab.dbf'
RMAN> recover datafile '/u01/oracle/db/sonu1/tab/sonu1_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@sonu
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@sonu
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@sonu
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@sonu
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.
SQL> shutdown abort;
SQL> startup nomount;
$ rman target / catalog rmancat/rmancat@sonu
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@sonu
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 data-file 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;
}</eof
</eof
</eof
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 data-file 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;
}</eof
</eof
</eof
RMAN SCRIPTS
--------------------------------------------------------------------------------
ALLOCATE CHANNEL
--------------------------------------------------------------------------------
ALLOCATE CHANNEL c1 DEVICE TYPE sbt
PARMS='SBT_LIBRARY=/mediavendor/lib/libobk.so ENV=(NSR_SERVER=tape_srv,NSR_
GROUP=oracle_tapes)';
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT '/disk1/%d_backups/%U';
ALLOCATE CHANNEL ch3 DEVICE TYPE sbt;
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;
----------------------------------------------------------------------------------------
CONFIGURE
---------------------------------------------------------------------------------------
CONFIGURE CHANNEL DEVICE TYPE sbt
PARMS='SBT_LIBRARY=/mediavendor/lib/libobk.so
ENV=(NSR_SERVER=tape_svr,NSR_CLIENT=oracleclnt,NSR_GROUP=ora_tapes)'
FORMAT "BACKUP_%U";
OTHERS
CONFIGURE MAXSETSIZE TO 7500K;
CONFIGURE MAXSETSIZE = 100M;
CONFIGURE AUXNAME FOR DATAFILE 2 TO '/newdisk/datafiles/df2.df;'
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/oradata/trgt/snap_trgt.ctl';
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CLEAR
CONFIGURE EXCLUDE FOR TABLESPACE cwmlite CLEAR;
CONFIGURE AUXNAME FOR DATAFILE 2 CLEAR;
CONFIGURE CHANNEL DEVICE TYPE sbt CLEAR;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CLEAR;
CONFIGURE DEVICE TYPE DISK CLEAR;
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
DUPLEXING
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE sbt TO 2;
DEVICE
CONFIGURE DEVICE TYPE sbt PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CHANNEL DISK
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE = 2G;
CONFIGURE CHANNEL DEVICE TYPE sbt RATE 1M;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT = /tmp/%U;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/save1/%U', '/save2/%U';
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE = 2M;
CONFIGURE CHANNEL 3 DEVICE TYPE DISK MAXPIECESIZE = 900K;
CONFIGURE CHANNEL DEVICE TYPE sbt PARMS 'ENV=(NSR_DATA_VOLUME_POOL=first_pool)';
CONTROL-FILE
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/%F';
OPTIMIZATION
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE BACKUP OPTIMIZATION CLEAR;
------------------------------------------------------------------
BACKUP SCRIPTS
-----------------------------------------------------------------
DATABASE
BACKUP DATABASE;
BACKUP DATABASE FORMAT '/tmp/%U', '?/dbs/%U', '?/oradata/%U';
BACKUP DEVICE TYPE sbt COPY OF DATABASE;
BACKUP DEVICE TYPE sbt COPY OF DATABASE DELETE INPUT;
BACKUP FORMAT = 'UW_%d/%t/%s/%p' ARCHIVELOG LIKE '%arc_dest%';
BACKUP TAG 'weekly_full_db_bkup' DATABASE MAXSETSIZE 10M;
BACKUP AS COMPRESSED BACKUPSET DEVICE TYPE DISK COPIES 2 DATABASE FORMAT '/u01/db_%U', '/u02/db_%U';
CONTROL-FILE
BACKUP DEVICE TYPE sbt CURRENT CONTROLFILE;
BACKUP CURRENT CONTROLFILE TO DESTINATION '/u01/backups';
BACKUP AS COPY CURRENT CONTROLFILE;
SPFILE
BACKUP SPFILE TO DESTINATION '/u01/backups';
TABLESPACE
BACKUP TABLESPACE users;
BACKUP TABLESPACE users FORMAT = '/tmp/users_%u%p%c';
BACKUP TABLESPACE tools MAXSETSIZE 5G;
BACKUP AS COPY TABLESPACE system, tools, users, undotbs;
DATAFILE
BACKUP DATAFILE 5,6,7;
BACKUP DATAFILE 5;
BACKUP DATAFILE 6;
BACKUP DATAFILE 7;
BACKUP DATAFILE 1 FORMAT '/u01/backups/%U', '/u02/backups/%U';
BACKUP DATAFILE 6 TO '/u01/backups', '/u02/backups';
BACKUP DEVICE TYPE DISK COPIES 3 DATAFILE 7 FORMAT '/tmp/%U','?/oradata/%U','?/%U';
ARCHIVELOG
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL;
BACKUP NOT BACKED UP SINCE TIME 'SYSDATE-14' DATABASE PLUS ARCHIVELOG;
BACKUP ARCHIVELOG ALL;
BACKUP ARCHIVELOG FROM SEQUENCE 288 UNTIL SEQUENCE 388 THREAD 1 DELETE INPUT;
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL FROM TIME 'SYSDATE-10' DELETE ALL INPUT;
PROXY
BACKUP DEVICE TYPE sbt PROXY DATAFILE 3;
BACKUP DEVICE TYPE sbt PROXY ONLY DATABASE;
BACKUP DEVICE TYPE sbt PROXY ONLY ARCHIVELOG ALL;
BACKUP-SET
BACKUP DEVICE TYPE sbt BACKUPSET ALL;
BACKUP COPIES 2 DEVICE TYPE sbt BACKUPSET ALL;
BACKUP DEVICE TYPE sbt BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT;
BACKUP DEVICE TYPE sbt BACKUPSET ALL;
BACKUP DEVICE TYPE sbt BACKUPSET ALL DELETE INPUT;
backup backupset ;
BACKUP DEVICE TYPE DISK AS BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP AS BACKUPSET DATAFILE 1;
BACKUP AS BACKUPSET (DATAFILE 3, 4, 5, 6, 7) (DATAFILE 8, 9);
BACKUP AS BACKUPSET DATAFILE 3, 4, 5, 6, 7, 8, 9;
BACKUP AS BACKUPSET DEVICE TYPE DISK COPIES 3 INCREMENTAL LEVEL 0 DATABASE;
BACKUP AS BACKUPSET
DATAFILE 1,2,3,4
CHANNEL ch1
CONTROLFILECOPY '/tmp/control01.ctl'
CHANNEL ch2
BACKUP AS BACKUPSET
ARCHIVELOG FROM TIME 'SYSDATE-14'
CHANNEL ch3;
TAG
BACKUP AS BACKUPSET TAG for_audit
COPY OF TABLESPACE users FROM TAG monday_users TABLESPACE SYSTEM FROM TAG monday_system;
BACKUP ARCHIVELOG ALL TAG first_copy;
DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;
BACKUP DEVICE TYPE sbt DATAFILECOPY FROM TAG 'LATESTCOPY' FORMAT 'Datafile%f_Database%d';
BACKUP TAG 'weekly_full_db_bkup' DATABASE MAXSETSIZE 10M;
POLICY BASED
BACKUP DATABASE KEEP UNTIL TIME "TO_DATE('31-DEC-2007' 'dd-mon-yyyy')" NOLOGS;
BACKUP DATABASE NOT BACKED UP SINCE TIME 'SYSDATE-1';
BACKUP MAXSETSIZE 10G DATABASE PLUS ARCHIVELOG;
BACKUP NOT BACKED UP SINCE TIME 'SYSDATE-1' MAXSETSIZE 10M DATABASE PLUS ARCHIVELOG;
BACKUP TABLESPACE users KEEP FOREVER NOLOGS;
BACKUP MAXSETSIZE = 100M ARCHIVELOG ALL;
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';
BACKUP DURATION 3:30 DATABASE;
BACKUP DURATION 4:00 TABLESPACE users;
BACKUP DURATION 4:00 PARTIAL TABLESPACE users FILESPERSET 1;
BACKUP DURATION 4:00 PARTIAL MINIMIZE TIME DATABASE FILESPERSET 1;
BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE FILESPERSET 1;
BACKUP DATABASE FORCE;
BACKUP ARCHIVELOG ALL FORCE;
EXCLUDING
CONFIGURE EXCLUDE FOR TABLESPACE cwmlite;
CONFIGURE EXCLUDE FOR TABLESPACE example;
BACKUP DATABASE NOEXCLUDE;
BACKUP TABLESPACE cwmlite, example;
CONFIGURE EXCLUDE FOR TABLESPACE cwmlite CLEAR;
CONFIGURE EXCLUDE FOR TABLESPACE example CLEAR;
BACKUP DATABASE
SKIP INACCESSIBLE
SKIP READONLY
SKIP OFFLINE;
--------------------------------------------------------------------
RESTORE
--------------------------------------------------------------------
RESTORE CONTROLFILE;
RESTORE CONTROLFILE FROM AUTOBACKUP;
RESTORE SPFILE FROM AUTOBACKUP;
RESTORE DATAFILE '?/oradata/trgt/tools01.dbf';
--------------------------------------------------------------------------------
RECOVERY
---------------------------------------------------------------------------------
RECOVER DATABASE NOREDO;
RECOVER TABLESPACE users, tools UNTIL LOGSEQ 1300 THREAD 1
RECOVER DATABASE TEST
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL TEST
RECOVER TABLESPACE users TEST
RECOVER DATABASE UNTIL CANCEL TEST
BLOCK RECOVERY
BLOCKRECOVER
DATAFILE 7 BLOCK 3
DATAFILE 2 BLOCK 235;
BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET;
BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM DATAFILECOPY;
BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199 FROM TAG = mondayam;
BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL 'SYSDATE-7';
BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL SCN 100;
BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL SEQUENCE 7024;
BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-10';
-------------------------------------------------------------
MANAGING
--------------------------------------------------------------
SHOWING
SHOW CHANNEL FOR DEVICE TYPE sbt;
SHOW DEFAULT DEVICE TYPE;
SHOW CHANNEL;
SHOW BACKUP OPTIMIZATION;
SHOW DATAFILE BACKUP COPIES;
SHOW EXCLUDE;
SHOW AUXNAME;
SHOW SNAPSHOT CONTROLFILE NAME;
show datafile backup copies;
show archivelog backup copies;
show controfile autobackup;
show device type;
show encryption algorithm;
show maxsetsize;
show retention policy;
show retention policy to redundancy;
CHECKING
CROSSCHECK BACKUPPIECE
CROSSCHECK BACKUPSET OF TABLESPACE UWDATA;
CROSSCHECK COPY OF ARCHIVELOG ALL;
CROSSCHECK CONTROLFILECOPY
CROSSCHECK DATAFILECOPY ALL;
CROSSCHECK FOREIGN ARCHIVELOG ALL;
CROSSCHECK PROXY 941;
BACKUP CHECK LOGICAL DATABASE;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
LIST BACKUP;
LIST BACKUP SUMMARY;
LIST SCRIPT NAMES;
LIST GLOBAL SCRIPT NAMES;
LIST ALL SCRIPT NAMES;
Reduntancy
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO NONE;
CHANGE BACKUP TAG 'db_archive_1' UNAVAILABLE;
CHANGE BACKUPSET TAG year_end_2002 KEEP FOREVER NOLOGS;
CHANGE BACKUPSET TAG year_end_2002 NOKEEP;
CHANGE BACKUPSET 2 NOKEEP;
DELETION
DELETE NOPROMPT BACKUP OF DATABASE COMPLETED BEFORE 'SYSDATE-7';
DELETE ARCHIVELOG ALL COMPLETED AFTER 'SYSDATE-7';
DELETE BACKUP DEVICE TYPE sbt;
DELETE BACKUP DEVICE TYPE DISK;
DELETE COPY;
DELETE COPY TAG 'LATESTCOPY';
DELETE SCRIPT 'full_backup';
DELETE GLOBAL SCRIPT 'global_full_backup';
VALIDATE
VALIDATE ARCHIVELOG ALL
VALIDATE ARCHIVELOG LIKE ''
VALIDATE ARCHIVELOG FROM SCN
VALIDATE ARCHIVELOG BETWEEN SCN AND SCN
VALIDATE ARCHIVELOG UNTIL SCN
VALIDATE ARCHIVELOG FROM SEQUENCE [THREAD ]
VALIDATE ARCHIVELOG SEQUENCE [THREAD ]
VALIDATE ARCHIVELOG SEQUENCE BETWEEN AND
VALIDATE ARCHIVELOG UNTIL SEQUENCE [THREAD ]
TIME BETWEEN '' AND ''
UNTIL TIME ''
VALIDATE CONTROLFILECOPY ALL
VALIDATE CONTROLFILECOPY ''
VALIDATE CONTROLFILECOPY LIKE ''
VALIDATE COPY OF DATABASE
VALIDATE COPY OF DATAFILE ''
VALIDATE COPY OF DATAFILE
VALIDATE COPY OF TABLESPACE '
VALIDATE CURRENT CONTROLFILE
VALIDATE DATABASE
VALIDATE DATAFILE ''
VALIDATE DATAFILE
VALIDATE DB_RECOVERY_FILE_DEST
VALIDATE RECOVERY AREA
VALIDATE RECOVERY FILES
VALIDATE SPFILE
VALIDATE TABLESPACE
OTHERS SCRIPTS
ENCRYTION
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION FOR TABLESPACE uwdata OFF;
SET ENCRYPTION ON
CONFIGURE ENCRYPTION FOR DATABASE OFF
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
CONFIGURE ENCRYPTION CLEAR;
CATALOG BASED
REGISTER DATABASE;
RESYNC CATALOG;
REPORT SCHEMA;
CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf';
CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf','/disk1/arch_logs/archive1_732.dbf';
CATALOG START WITH '/disk1/backups/';
UNREGISTER DATABASE;
OTHERS
SET NEWNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '/tmp/tools01.dbf';
SWITCH DATAFILE '/tmp/tools01.dbf' TO DATAFILECOPY '?/oradata/trgt/tools01.dbf';
RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP;
DUPLICATE TARGET DATABASE TO 'dupdb';
DUPLICATE TARGET DATABASE TO dupdb NOFILENAMECHECK UNTIL TIME 'SYSDATE-7';
INCREMENTAL
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DIFFERENTIAL TABLESPACE users;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE SKIP INACCESSIBLE DATABASE;
************************************************END************************************
No comments:
Post a Comment